🗂️ Caso Abierto — SQL Detective

Un misterio de fraude corporativo que se resuelve escribiendo SQL.

Eres el auditor. Alguien adentro de la empresa desvió dinero. Los registros del sistema son tu única evidencia.

---

Inspirado en SQL Murder Mystery de Knight Lab, con dos diferencias de fondo:

1. El escenario es un fraude corporativo con esquema empresarial realista: notas de venta, notas de crédito, credenciales, turnos, ausencias y logs de acceso a terminales.
2. El juego corre contra un backend que ejecuta SQL arbitrario de desconocidos de forma segura — y ese sandbox es el verdadero proyecto de ingeniería aquí.

🕵️ El caso: "Notas fantasma"

Auditoría interna de Nortek Distribuciones S.A. detectó el 3 de marzo de 2025 que durante febrero se emitieron notas de crédito fraudulentas: los reembolsos nunca llegaron a los clientes. Encuentra las notas falsas, sigue el rastro y acusa al responsable. Cuidado: no todo lo que parece sospechoso lo es.

• **9 tablas Nortek Distribuciones S.A. ** para explorar, ninguna de más.
• 4 saltos de pista, cada uno exige una técnica SQL distinta (de WHERE con JOIN hasta window functions).
• Pistas falsas sembradas a propósito, con salida lógica: se descartan con la query correcta, no adivinando.
• 10 intentos de acusación por sesión, con feedback específico si acusas al sospechoso equivocado.

🚀 Cómo correrlo

git clone https://github.com/huxwell-src/caso-abierto.git
cd caso-abierto
pip install -r requirements.txt
uvicorn app.main:app

Abre http://127.0.0.1:8000 — el juego explica las reglas al entrar. Ctrl+Enter ejecuta la consulta.

python -m pytest    # 48 tests: ataques al sandbox, resolubilidad del caso, API e2e

🧱 Arquitectura

app/
├── sandbox.py     Ejecución segura de SQL arbitrario (el corazón del proyecto)
├── engine.py      Motor de casos: los misterios son data, no código
├── sessions.py    Sesiones en memoria: consultas, pistas, intentos
└── main.py        API FastAPI + frontend estático
cases/
└── fraude-nortek/
    ├── case.json  Briefing, pistas, solución, feedback por acusación errónea
    ├── seed.py    Generación reproducible con asserts de coherencia
    └── case.db    SQLite generada por seed.py (inmutable en runtime)
static/
└── index.html     Frontend single-file: CodeMirror + resultados + notas
tests/
├── test_sandbox.py   26 intentos de escape + límites + cadena de resolución
└── test_api.py       Sesión, query, pistas, acusación, rate limit

Decisión clave — motor ≠ contenido. Un caso es un directorio con case.json + seed.py. El motor los descubre al arrancar y regenera la base si falta. Agregar un misterio nuevo no toca una línea del motor.

¿Por qué backend, si el original corre en el navegador? SQL Murder Mystery usa SQLite client-side; funciona porque la solución viaja al cliente. Aquí la solución nunca sale del servidor: la validación de acusaciones es server-side con feedback parcial, hay límite de intentos contra la fuerza bruta, y las sesiones registran cuántas consultas y pistas costó resolver — la base de un leaderboard futuro.

🔒 Modelo de amenazas del sandbox

El problema a resolver: dejar que extraños ejecuten SQL arbitrario contra mi servidor sin que me lo destruyan.

Amenaza	Defensa
Escritura / DDL (DROP, INSERT, triggers, vistas)	Conexión mode=ro&immutable=1 más authorizer de SQLite que solo permite SELECT/READ — dos capas independientes
Escape del archivo (ATTACH, VACUUM INTO)	Denegado por el authorizer
Metaoperaciones (PRAGMA writable_schema, ANALYZE)	Denegado por el authorizer
Código nativo (load_extension)	Deshabilitado por defecto en sqlite3 de Python + fuera de la lista blanca
Funciones peligrosas (readfile, writefile, …)	Lista blanca de funciones: lo no listado se deniega, incluido lo que no conozco todavía
Queries eternas (CTE recursiva infinita, CROSS JOIN múltiple)	Progress handler con presupuesto de 2 s
Resultados gigantes	Tope de 200 filas y 500 caracteres por celda
Segundo statement (SELECT 1; DROP …)	Un solo statement por request, rechazo explícito
Estado compartido entre jugadores	Conexión nueva por request sobre base inmutable
Fuerza bruta de la acusación	Máximo 10 intentos por sesión (HTTP 429)
Agotamiento de memoria por sesiones	TTL de 6 h + tope duro con desalojo del más viejo

Cada fila tiene al menos un test en tests/test_sandbox.py. Si encuentras un escape que no está cubierto, abre un issue: el modelo de amenazas vale lo que vale su suite de ataques.

Límites asumidos: el timeout tiene granularidad de ~5.000 operaciones de VM, las sesiones son en memoria (una sola instancia) y no hay aislamiento de proceso — un bug de memoria en SQLite mismo no está mitigado. Para exposición pública real, ver fase 3 del roadmap.

🧩 Anatomía del misterio

La curva de dificultad está implícita en las técnicas que exige cada salto:

Salto	Técnica SQL	Qué revela
1	JOIN múltiple + WHERE de desigualdad	Las notas de crédito cuyo reembolso no vuelve al cliente
2	JOIN con rango de fechas	El dueño de la credencial emisora no estaba ese día
3	GROUP BY + HAVING	Una sola persona estuvo en ese terminal las tres veces
4	Window function / subconsulta + LIKE	El último login antes de cada emisión, y el vínculo que cierra el motivo

Los datos nunca se cargan a mano: seed.py es reproducible (seed fijo) y termina con cuatro assert que verifican que la cadena de pistas apunta al culpable y solo a él. Si tocas los datos y rompés la lógica, el seed falla antes de generar la base.

⚠️ SPOILER — la solución completa, query por query

Está escrita como tests ejecutables en tests/test_sandbox.py::TestMysteryIsSolvable: cada salto de la investigación es un test con la query exacta y el resultado esperado, incluidos los tests que demuestran que las pistas falsas se descartan con la consulta correcta.

📡 API

Método	Ruta	Descripción
GET	/api/cases	Casos disponibles
GET	/api/cases/{id}	Briefing (nunca expone la solución)
GET	/api/cases/{id}/schema	Tablas, columnas y conteos
POST	/api/cases/{id}/session	Abre una sesión de juego
POST	/api/cases/{id}/query	Ejecuta SQL sandboxeado (X-Session-Id requerido)
POST	/api/cases/{id}/hint	Siguiente pista secuencial (queda registrada)
POST	/api/cases/{id}/accuse	Acusación con validación server-side y feedback parcial

Documentación interactiva en /docs (Swagger generado por FastAPI).

🤝 Contribuir un caso

Los misterios son contenido, no código. Para proponer uno nuevo:

1. Crea cases/tu-caso/ con case.json (briefing, pistas, solución, feedback por sospechoso) y seed.py.
2. El seed debe ser reproducible (seed fijo) y terminar con asserts que demuestren que la cadena de pistas es coherente.
3. Reglas de diseño que funcionan: 6–9 tablas, 3–5 saltos con técnicas SQL distintas, al menos una pista falsa con salida lógica.
4. Agrega los tests de resolubilidad del caso (mira TestMysteryIsSolvable como plantilla).

🗺️ Roadmap

• Fase 1 — Un caso completo, sandbox con suite de ataques, motor de casos, verificación server-side, frontend austero.
• Fase 2 — Leaderboard (consultas + pistas + tiempo), sesiones en Redis, segundo caso como prueba de que el motor escala por contenido.
• Fase 3 (exploración) — Generación procedural de variantes: aleatorizar nombres, fechas y culpable manteniendo la coherencia lógica de las pistas. Es un problema de satisfacción de restricciones disfrazado; se aborda solo con las fases anteriores sólidas.

🛠️ Stack

Python 3.12 · FastAPI · SQLite (stdlib, sin ORM a propósito: el juego es SQL) · CodeMirror · pytest

---

Diseño, datos y paranoia: Nicolas Sanchez Berrios · MMXXVI