Cookbook SQL: validar datos en la base

Por qué mirar la base

Una API puede responder 201 Created y guardar mal los datos. Estos son casos que solo se ven con una query:

Response de la API: 201 Created
{ "message": "Order created successfully", "order_id": "ord_123" }

Lo que hay en la base:
- order_items está vacío
- total vale 0 cuando debería valer 150
- user_id es NULL

En la exploración de un ticket, la UI muestra lo que ve el usuario, la API muestra el contrato y la base muestra lo que realmente quedó guardado. Esta página cubre la tercera parte. La parte API está en Postman y en OpenAPI.

Conexión y herramientas

El agente consulta la base a través del MCP de DBHub. La configuración (dbhub.toml, las variables DBHUB_* de .env y cómo diagnosticar una conexión que falla) está en DBHub.

DBHub nombra sus tools como {tool}_{source_id}. El repo trae una sola fuente, primary, así que el agente tiene dos:

En la práctica le pides al agente algo como "acabo de crear una orden, revisa que el total coincida con sus items" y él arma y corre la query. Las recetas de abajo sirven para que sepas qué pedir y para leer lo que te devuelve. Para validar alcanza un usuario de solo lectura (SELECT).

Dialectos

Las queries de esta página son de Postgres. DBHUB_TYPE en .env te dice en qué motor estás; estas son las diferencias que más aparecen:

Cinco cosas que validar

Existencia de un usuario recién registrado y de los items de una orden:

SELECT COUNT(*) FROM users WHERE email = 'test@example.com';
-- Esperado: 1

SELECT COUNT(*) FROM order_items WHERE order_id = 'ord_123';
-- Esperado: mayor que 0

Side effects: después de borrar el usuario usr_123 desde la UI o la API, ninguna tabla dependiente debería conservar filas suyas (si el schema declara ON DELETE CASCADE o la aplicación limpia por su cuenta):

SELECT COUNT(*) FROM orders        WHERE user_id = 'usr_123';  -- Esperado: 0
SELECT COUNT(*) FROM sessions      WHERE user_id = 'usr_123';  -- Esperado: 0
SELECT COUNT(*) FROM notifications WHERE user_id = 'usr_123';  -- Esperado: 0

Huérfanos (foreign keys rotas)

Items que apuntan a una orden que ya no existe:

SELECT oi.*
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
-- Esperado: 0 filas

Duplicados

-- Emails repetidos
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Mismo usuario, mismo día, mismo total (típico de un doble clic en "Pagar")
SELECT user_id, CAST(created_at AS DATE), total, COUNT(*)
FROM orders
GROUP BY user_id, CAST(created_at AS DATE), total
HAVING COUNT(*) > 1;

Totales que no cuadran

Órdenes cuyo total guardado no coincide con la suma de sus items:

SELECT o.id, o.total, SUM(oi.price * oi.quantity) AS calculated
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.total
HAVING o.total <> SUM(oi.price * oi.quantity);

Y un descuento aplicado sobre una orden puntual:

SELECT subtotal, discount_amount, total,
       subtotal - discount_amount AS expected_total
FROM orders
WHERE id = 'ord_123';
-- Esperado: total = expected_total

Si el dinero está en columnas de punto flotante, la igualdad exacta falla por redondeo. Compara con una tolerancia o castea al tipo decimal de la columna.

Timestamps

SELECT * FROM users    WHERE created_at > NOW();          -- en el futuro
SELECT * FROM products WHERE updated_at < created_at;    -- actualizado antes de crearse
SELECT * FROM orders   WHERE created_at IS NULL;         -- falta donde es obligatorio

-- Se creó hace menos de un minuto?
SELECT * FROM users
WHERE email = 'test@example.com'
  AND created_at > NOW() - INTERVAL '1 minute';
-- Esperado: 1 fila

El reloj del servidor de la aplicación y el de la base pueden diferir por segundos. Si una ventana de "último minuto" no encuentra la fila, amplíala antes de reportar un bug.

Antes y después de una acción

La receta más útil durante la exploración: lees un valor, haces la acción por UI o API y lo vuelves a leer. El assert es sobre la diferencia.

1. Antes

   SELECT stock FROM products WHERE id = 'prod_123';
   -- Anota el valor, por ejemplo 40

2. Acción

   Crea desde la UI o la API una orden que compre 5 unidades de prod_123.

3. Después

   SELECT stock FROM products WHERE id = 'prod_123';
   -- Esperado: 35 (40 - 5)

Cuatro estrategias

Snapshot antes / después

La receta de arriba: leer, actuar, leer, comparar el delta.

Comparar contra la fuente de verdad

Recalcular lo que la fila debería tener (suma de items, subtotal menos descuento) y compararlo con lo guardado.

Restricciones de negocio

Buscar filas que no deberían existir. El resultado esperado es cero:

SELECT COUNT(*) FROM wallets WHERE balance < 0;  -- Esperado: 0

Cruce entre tablas

Varias tablas deben coincidir después de una acción (orden, items, inventario, notificación). Por ejemplo, ninguna orden activa sin items:

SELECT o.id
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.id
HAVING COUNT(oi.id) = 0;
-- Esperado: 0 filas

Qué validar según el nivel de test

Checklist

[ ] Las filas existen en las tablas correctas
[ ] Valores numéricos correctos (totales, cantidades)
[ ] Foreign keys válidas, sin huérfanos
[ ] Sin duplicados inesperados
[ ] Timestamps plausibles
[ ] Las restricciones de negocio se cumplen
[ ] Side effects (triggers, cascades) ejecutados
[ ] Transacciones confirmadas o revertidas según lo esperado

Lo que no se hace