Projeto PostgreSQL: banco relacional de pedidos

O projeto final é um banco de pedidos com clientes, pedidos e itens. Você vai fazer o PostgreSQL garantir relações, registrar uma compra como unidade e gerar um histórico. Pagamento, estoque real, API e deploy ficam fora do escopo; aqui o resultado é um esquema verificável e consultas que você consegue explicar.

Pense numa comanda. O cabeçalho identifica cliente e estado; as linhas registram os itens. Se uma linha falha, a comanda não deve ficar pela metade. Tabelas e foreign keys representam a estrutura; uma transação agrupa as mudanças. O limite da analogia é que concorrência, isolamento e recuperação têm regras técnicas que uma comanda de papel não reproduz.

Crie o esquema a partir das invariantes

CREATE TABLE clientes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome text NOT NULL CHECK (nome <> ''),
  email text NOT NULL UNIQUE
);

CREATE TABLE pedidos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL REFERENCES clientes(id),
  status text NOT NULL CHECK (status IN ('pendente', 'pago', 'cancelado')),
  total_centavos bigint NOT NULL CHECK (total_centavos >= 0),
  criado_em timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE itens_pedido (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  pedido_id bigint NOT NULL REFERENCES pedidos(id) ON DELETE CASCADE,
  produto text NOT NULL,
  quantidade integer NOT NULL CHECK (quantidade > 0),
  preco_unitario_centavos bigint NOT NULL CHECK (preco_unitario_centavos >= 0)
);

O preço fica congelado no item porque o histórico não deve mudar quando o produto recebe novo preço. ON DELETE CASCADE aqui significa que itens não têm sentido sem o pedido; avalie retenção e auditoria antes de permitir exclusão do pedido num sistema real.

Trate o esquema como código versionado. Cada alteração deve virar uma migração revisável, com estratégia para linhas que já existem e plano de retorno. Adicionar uma coluna obrigatória a uma tabela cheia, por exemplo, pede valor inicial ou etapas graduais. Teste a migração numa cópia descartável antes de aplicá-la a um ambiente compartilhado.

Registre a unidade dentro de uma transação

BEGIN;

INSERT INTO pedidos (cliente_id, status, total_centavos)
VALUES (1, 'pendente', 18990)
RETURNING id;

INSERT INTO itens_pedido
  (pedido_id, produto, quantidade, preco_unitario_centavos)
VALUES
  (201, 'Teclado', 1, 14990),
  (201, 'Mousepad', 2, 2000);

COMMIT;

No código da aplicação, use o ID devolvido pelo primeiro comando; não presuma 201. Se o segundo INSERT violar uma restrição, execute ROLLBACK. Esse é o erro controlado: tente quantidade zero num banco descartável e confirme que, após o rollback, nem pedido nem itens daquela tentativa ficaram gravados.

O total também precisa de uma fonte definida. Neste projeto, a aplicação calcula e o banco exige valor não negativo. Em um sistema financeiro, considere regras mais fortes para conferir soma, descontos e arredondamento. Não use ponto flutuante binário para valores que precisam de centavos exatos.

Produza um histórico compreensível

SELECT
  p.id,
  c.nome AS cliente,
  p.status,
  p.total_centavos,
  p.criado_em
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
WHERE p.cliente_id = $1
ORDER BY p.criado_em DESC, p.id DESC
LIMIT $2;

Os parâmetros mantêm valores separados do SQL. Data e ID criam uma ordem estável. Para a leitura frequente, um candidato é:

CREATE INDEX pedidos_cliente_criado_idx
ON pedidos (cliente_id, criado_em DESC, id DESC);

Use EXPLAIN (ANALYZE, BUFFERS) com volume representativo. O índice custa escrita e espaço, então sua existência precisa responder a uma consulta real.

Feche com provas, não com impressão

Verifique cinco pontos: e-mail duplicado falha; pedido para cliente inexistente falha; quantidade zero desfaz a transação; histórico devolve o cliente correto; e a ordem permanece estável com datas iguais. Registre comando, saída esperada e saída observada. Uma divergência aponta para tipo, restrição, filtro, join ou transação a investigar.

O laboratório usa JSON local e um simulador didático de SQL básico. Ele aceita SELECT em uma tabela, uma condição simples, ORDER BY e LIMIT. Não executa joins, INSERT, restrições, transações, índices nem PostgreSQL real. A consulta inicial deve devolver dois pedidos pagos em ordem de total.

Sua missão final é executar o roteiro num banco isolado, incluindo o erro com rollback, e guardar as saídas. Depois explique cada garantia numa frase: quem protege a identidade, quem protege a relação e quem impede uma compra pela metade. Esse mapa encerra o curso com um banco que você consegue testar e defender tecnicamente.