Skip to content

About

Investigação de defeitos de dados num CRM/ERP de vendas, com SQL documentado contra um PostgreSQL real.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

🔎 SQL Defect Investigation

Português · English · Español

CI license postgres

Descrição

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.


Modelo de dados

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)"
    }
Loading

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.


Estrutura do Projeto

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

Como rodar

Opção 1 — Docker (recomendado)

docker compose up -d --wait

Isso 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_investigation

Para derrubar o banco e apagar os dados (na próxima subida, tudo é recarregado do zero):

docker compose down -v

Opção 2 — Validação local sem Docker

npm install
npm run validate

Isso 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 relatorios

Isso 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.


Integração contínua

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 validate e npm 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 o exploracao.sql de 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.


Os 9 defeitos investigados

# 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

Principais achados

# 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.

Como reproduzir uma investigação

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.sql

Basta trocar a pasta para rodar as outras investigações.

Implementação

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/.

Quando a massa de dados muda

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 é:

  1. Corrigir o README.md da investigação apontada, com os números novos.
  2. Rodar npm run relatorios:atualizar, que regrava os arquivos resultado-esperado.json.
  3. Conferir no diff do PR que os números da referência e do relatório são os mesmos.

Licença

Distribuído sob a licença MIT. Veja LICENSE para mais detalhes.

About

Investigação de defeitos de dados num CRM/ERP de vendas, com SQL documentado contra um PostgreSQL real.

Topics

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages