Este repositório simula um banco de dados de CRM/ERP de vendas com defeitos de dados reais — clientes duplicados, foreign keys órfãs, status inconsistentes, datas inválidas, pagamentos em duplicidade, valores de mentira em campos obrigatórios, texto com codificação quebrada, duplicidade aproximada no catálogo e fuso horário misturado nas datas — e documenta as investigações SQL que encontram cada um deles, com a mesma metodologia usada em investigação de bugs de produção: sintoma → hipóteses → queries e evidências → causa raiz → impacto → correção proposta.
Não é um exercício de sintaxe SQL: o objetivo é demonstrar a habilidade de usar SQL para encontrar defeito real num banco com dados já corrompidos, do jeito que aconteceria investigando um bug relatado em produção.
Projeto relacionado: a continuação é o sql-performance-investigation, sobre o mesmo CRM/ERP com volume realista de dados. Lá o dado está certo, mas a consulta está lenta, e a evidência vem dos planos de execução (
EXPLAIN ANALYZE).
Nota sobre os dados: todo o schema e os dados aqui são fictícios, criados especificamente para este projeto de portfólio. Nenhuma informação de empregador atual ou anterior foi usada.
Um CRM/ERP de vendas: clientes fazem pedidos, que podem ser atendidos por um vendedor, contêm itens do catálogo de produtos e recebem pagamentos.
erDiagram
clientes ||--o{ pedidos : "faz"
vendedores |o--o{ pedidos : "atende"
pedidos ||--o{ itens_pedido : "contém"
produtos ||--o{ itens_pedido : "aparece em"
pedidos ||--o{ pagamentos : "recebe"
clientes {
int id PK
varchar nome
varchar email "sem UNIQUE (inv. 01), sem validação (inv. 06)"
varchar documento "sem UNIQUE (inv. 01), sem validação (inv. 06)"
varchar telefone "opcional"
timestamp data_cadastro
varchar status "ativo | inativo"
}
vendedores {
int id PK
varchar nome
varchar email UK
varchar equipe "opcional"
}
produtos {
int id PK
varchar nome "texto sem validação (inv. 07), sem UNIQUE (inv. 08)"
numeric preco "CHECK >= 0"
varchar categoria "opcional, texto sem validação (inv. 07)"
}
pedidos {
int id PK
int cliente_id FK "NOT VALID na massa quebrada (inv. 02)"
int vendedor_id FK "opcional"
timestamp data_pedido "sem fuso (inv. 09)"
varchar status "sem CHECK (inv. 03)"
numeric valor_total "= soma dos itens (validacoes/)"
}
itens_pedido {
int id PK
int pedido_id FK
int produto_id FK
int quantidade "CHECK > 0"
numeric preco_unitario
}
pagamentos {
int id PK "único identificador: sem id da transação do gateway (inv. 05)"
int pedido_id FK
numeric valor
timestamp data_pagamento "opcional, sem validação (inv. 04), sem fuso (inv. 09)"
varchar status "sem CHECK (inv. 03)"
}
As anotações inv. 01 a inv. 09 marcam a característica do schema que é a causa raiz de cada defeito investigado. Por exemplo, a falta de UNIQUE em email e documento é o que permite os clientes duplicados da investigação 01.
sql-defect-investigation/
├── .github/workflows/
│ └── ci.yml # CI: validação com Postgres embarcado + Docker Compose
├── investigacoes/
│ ├── 01-clientes-duplicados/
│ │ ├── README.md # relatório: sintoma → hipóteses → evidência → causa raiz → correção
│ │ ├── exploracao.sql # queries na ordem em que foram usadas
│ │ ├── deteccao.sql # query final de detecção (executada pela CI)
│ │ └── resultado-esperado.json # resultado de cada query do relatório (referência do teste)
│ ├── 02-fk-orfa/ # mesma estrutura
│ ├── 03-status-inconsistente/ # mesma estrutura
│ ├── 04-data-invalida/ # mesma estrutura
│ ├── 05-pagamento-duplicado/ # mesma estrutura
│ ├── 06-dados-de-mentira/ # mesma estrutura
│ ├── 07-codificacao-quebrada/ # mesma estrutura
│ ├── 08-duplicidade-aproximada/ # mesma estrutura
│ └── 09-fuso-horario/ # mesma estrutura
├── validacoes/ # regras de consistência de valores (total, pagamento, catálogo)
├── sql/
│ ├── 01_schema.sql # DDL: tabelas, FKs, constraints
│ ├── 02_seed_valido.sql # massa de dados limpa (controle)
│ └── 03_seed_quebrado.sql # defeitos introduzidos de propósito, comentados
├── scripts/
│ ├── validate.js # sobe um Postgres real e valida schema + as duas massas
│ ├── relatorios.js # confere se os relatórios continuam batendo com o banco
│ └── postgres-embarcado.js # funções compartilhadas pelos dois scripts
├── docker-compose.yml # Postgres pronto, já carregado com os dados
├── package.json
├── LICENSE
├── README.md # português
├── README.en.md # inglês
└── README.es.md # espanhol
docker compose up -d --waitIsso sobe um PostgreSQL 16 na porta 5432 já com o schema e as duas massas de dados carregadas (os arquivos em sql/ rodam automaticamente, em ordem, na primeira inicialização do container). O --wait só devolve o terminal quando o banco termina de carregar.
Para abrir um terminal SQL no banco:
docker compose exec postgres psql -U postgres -d defect_investigationPara derrubar o banco e apagar os dados (na próxima subida, tudo é recarregado do zero):
docker compose down -vnpm install
npm run validateIsso baixa um binário real do PostgreSQL 16, a mesma versão usada no Docker, sem precisar de Docker instalado. Em seguida, sobe um banco temporário, carrega o schema e as duas massas de dados, e confirma que cada defeito esperado é encontrado pelas queries de investigação — e que a massa "válida", sozinha, não aciona nenhuma delas.
npm run relatoriosIsso roda o exploracao.sql de cada investigação e compara o resultado de cada query com o resultado-esperado.json da pasta, que guarda os mesmos números mostrados no relatório. Se a massa de dados mudar e algum relatório ficar desatualizado, esse comando falha e aponta a investigação, a consulta e a linha que mudaram.
A cada push na main e a cada pull request, o GitHub Actions roda dois jobs em paralelo:
- Validação (Postgres embarcado): executa
npm run validateenpm run relatorios, os mesmos comandos da Opção 2. - Docker Compose: sobe o banco com
docker compose up --wait, confere que o schema e as duas massas de dados foram carregados e executa oexploracao.sqlde cada investigação. Isso garante que a Opção 1 funciona e que toda query mostrada nos relatórios roda sem erro.
O resultado de cada conferência aparece numa tabela no resumo da execução, na aba Actions do GitHub, sem precisar abrir os logs.
As dependências são mantidas pelo Dependabot (.github/dependabot.yml): uma vez por mês, ele abre no máximo um PR para as dependências npm e um para as actions da CI, que passam pela mesma CI. A versão major do embedded-postgres fica de fora, porque ela define a versão do Postgres da validação local, que precisa continuar igual à do Docker.
| # | Defeito | Cenário real simulado | Investigação |
|---|---|---|---|
| 1 | Clientes duplicados | Formulário de cadastro enviado duas vezes, sem constraint única em email/documento | Relatório |
| 2 | Foreign key órfã | Cliente removido diretamente no banco (acesso de suporte), sem tratar os pedidos vinculados | Relatório |
| 3 | Status inconsistente | Pedido marcado "entregue" sem pagamento aprovado; pedido "cancelado" com pagamento ainda "aprovado" | Relatório |
| 4 | Data inválida | Pagamento lançado à mão na conciliação com a data digitada errada, anterior ao próprio pedido | Relatório |
| 5 | Pagamento em duplicidade | O gateway reenviou a confirmação do pagamento (webhook) e a aplicação registrou as duas, sem chave de idempotência | Relatório |
| 6 | Valores de mentira em campos obrigatórios | No cadastro por televendas, e-mail e CPF são obrigatórios, e o atendente digitou valores de preenchimento quando o cliente não informou | Relatório |
| 7 | Texto com codificação quebrada | A sincronização do catálogo com o ERP leu um arquivo UTF-8 como Windows-1252 | Relatório |
| 8 | Duplicidade aproximada no catálogo | A busca da tela de venda é exata, não achou o produto, e o vendedor cadastrou outro com o nome digitado errado | Relatório |
| 9 | Fuso horário misturado nas datas | O app grava a data do pedido em UTC, enquanto o site e o gateway gravam no horário de Brasília, numa coluna que não guarda o fuso | Relatório |
| # | O que a investigação encontrou | Causa raiz |
|---|---|---|
| 1 | Os clientes 1 e 11 são a mesma pessoa, cadastrados com 2min32s de diferença e o nome redigitado, o que descarta duplo clique e aponta para reenvio manual do formulário | A tabela clientes não tem nenhuma restrição de unicidade em e-mail ou documento |
| 2 | O pedido 10 (R$ 99,90) aponta para um cliente removido e some de todo relatório que faz JOIN com clientes |
O cliente foi apagado num período sem a foreign key, que depois foi recriada como NOT VALID e nunca validada |
| 3 | Um pedido cancelado com o pagamento ainda aprovado (sem estorno), um pedido entregue sem pagamento confirmado e o pedido 10 confirmado sem pagamento | Os status de pedido e de pagamento mudam de forma independente, e o banco não tem nenhuma regra sobre eles |
| 4 | Um pagamento datado 48h antes do próprio pedido, com o mesmo horário dele, o que distorce o indicador de tempo até aprovação | Lançamento manual com a data digitada errada, sem validação de data nem registro da origem |
| 5 | O pedido 2 tem dois pagamentos aprovados idênticos, com 4 segundos de diferença: o sistema registra R$ 199,80 recebidos para uma única transação de R$ 99,90 no gateway | A tabela pagamentos não guarda o id da transação do gateway, então um webhook reenviado vira um pagamento novo |
| 6 | Três cadastros barrados na análise de crédito: dois com o e-mail naotem@naotem.com e CPFs de mentira (que passam no dígito verificador) e um com dois dígitos do CPF trocados |
NOT NULL só garante que o campo não está vazio: nada valida o conteúdo, e o formulário não oferece "não informado" |
| 7 | Produtos com nomes como "Módulo de Relatórios" em e-mails de 5 pedidos, enquanto o "Plano Básico Mensal" continua certo: o defeito está no dado, não na exibição | A sincronização com o ERP lê o arquivo UTF-8 como Windows-1252, e a operação inversa devolve o texto original exatamente, o que prova o diagnóstico |
| 8 | "Consultoria Avulsa (hora)" e "Consutoria avulsa - hora" são o mesmo serviço, com as vendas divididas entre os dois. Nenhuma comparação exata os liga, mas a similaridade de texto (pg_trgm) dá 0.808, e o limite padrão da extensão (0.3) marcaria planos legítimos como duplicados |
produtos.nome não tem nenhuma restrição de unicidade, e a busca exata da tela de venda empurra o vendedor a cadastrar de novo |
| 9 | Dois pedidos feitos pelo app às 22:30 e 23:10 de 31/01 aparecem em fevereiro, com o pagamento 2h55 "antes" do pedido. Descontadas as 3 horas do fuso, o pagamento volta ao padrão normal (5 minutos depois). Janeiro fica R$ 149,80 abaixo do real | As colunas de data são timestamp without time zone e não guardam o fuso, então UTC e horário de Brasília se misturam sem o banco perceber |
Cada relatório termina com uma correção proposta, testada numa cópia descartável do banco, mas não executada: como numa investigação real, o QA entrega evidência e recomendação, e a decisão de mexer nos dados é do time responsável.
Com o banco no ar (Opção 1), cada exploracao.sql roda as queries do relatório na mesma ordem dos passos:
docker compose exec -T postgres psql -U postgres -d defect_investigation < investigacoes/01-clientes-duplicados/exploracao.sqlBasta trocar a pasta para rodar as outras investigações.
Cada defeito está implementado e comentado em sql/03_seed_quebrado.sql. Cada investigação fica em investigacoes/, com um relatório completo e a query de detecção (deteccao.sql), que a CI executa contra as duas massas de dados. Assim, o que está documentado é exatamente o que é testado.
Além dos 9 defeitos, a validação confere regras de consistência de valores (total do pedido = soma dos itens, pagamentos aprovados = total do pedido, e o preço do item igual ao do catálogo na massa de controle). As regras e o motivo de cada uma estão em validacoes/.
Os relatórios mostram resultados reais das queries, então uma mudança em sql/ pode deixá-los desatualizados. Nesse caso, o npm run relatorios falha (e a CI também). O caminho é:
- Corrigir o
README.mdda investigação apontada, com os números novos. - Rodar
npm run relatorios:atualizar, que regrava os arquivosresultado-esperado.json. - Conferir no diff do PR que os números da referência e do relatório são os mesmos.
Distribuído sob a licença MIT. Veja LICENSE para mais detalhes.