September 26, 2026
I like detective stories and I like SQL, and at some point they stopped being two things. Both are the same move: you have a question, a pile of evidence that doesn't answer it directly, and you poke at it until it does.
So I built a game where the clues are tables and the only tool is a SQL console. Twelve rows the first night, a door reader, a missing file. You write queries until you know who did it.
The rule that turned it from a quiz into a game came later, and it's the part worth writing about: naming the culprit isn't enough. You hand in the query that proves it.
The first implementation was the obvious one. You type the name, the engine compares it with the expected answer, done. It worked, and it was boring, because the SQL was optional. You could stare at twelve rows, spot the name, type it, and the game would congratulate you on your query skills.
So I added the proof: next to the accusation you submit the query that supports it. The game runs it and checks that the answer shows up in the result.
That lasted until I tried to break it:
SELECT '<the culprit>' AS name, '<the time>' AS timeAccepted. The one bit of inference the game asked for was, in practice, optional. And now it came with a rubber stamp.
The fix is two clauses, in the order a prosecutor would say them. From engine/catalog.py:
def proof_derives(case, sql, lang="es"):
limpio = _sin_comentarios(sql)
tablas = {t.lower() for t in case_tables(case, lang)}
tocadas = {m.group(1).lower().strip('"').split(".")[-1]
for m in _TABLA.finditer(limpio)}
if not (tocadas & tablas):
return {"ok": False, "motivo": "sin_datos"}
...One: the query has to read something. A SELECT of literals is not a proof, it's a statement. I match FROM and JOIN targets against the tables the case actually ships, so reading some unrelated table doesn't count either.
Two: the query can't contain, typed by hand, the value it claims to show. Filtering by the answer is assuming the conclusion. If the culprit is called Quiroga, WHERE name = 'Quiroga' proves nothing. You already knew.
The second rule is where it gets interesting.
First attempt: collect every literal in the query and compare it with the answer. Fine for strings. Then someone filters by date:
WHERE moment >= TIMESTAMP '2019-03-12 21:00'If the answer to "how many visits" is 12 and you scan the raw query text for numbers, that 12 inside the date is a hit. A perfectly good time filter gets rejected as cheating, which is the worst kind of bug: the player did it right and the game called them a liar.
The fix is plain and I like it anyway. Look for numbers outside the strings, because strings are already compared whole:
sin_cadenas = _LITERAL.sub(" ", limpio)
literales |= {normalize(x) for x in re.findall(r"\b\d+(?:\.\d+)?\b", sin_cadenas)}The second thing is a rule I wrote for a case that doesn't exist yet. The seasons run as one thread, and sooner or later a case will ask for something you learned three cases ago, like an account number. Typing it then isn't cheating, it's remembering. So an answer field can opt out with literal_ok: true. Nothing uses it today. I'd rather have the escape hatch ready than loosen the rule for everyone the first time a case needs it.
Neither of these is clever. Both came from watching the thing reject queries it shouldn't, which is the only reliable way I know to find rules like this.
The game runs fully client side: DuckDB compiled to WebAssembly, no server, nothing to sign up for. Which raises the obvious question. If validation happens on the player's machine, isn't the answer sitting right there?
The catalog doesn't carry the answers. It carries SHA-256 hashes of the normalized accepted values, and the same two rules run against those:
const aceptados = new Set(campo.hashes || []);
for (const bruto of [...textos, ...numeros]) {
const clave = normalizar(bruto);
if (!clave) continue;
if (!hashes.has(clave)) hashes.set(clave, await sha256(clave));
if (aceptados.has(hashes.get(clave))) return { ok: false, motivo: "literal", campo: campo.key };
}Normalizing first (lowercase, no accents, no punctuation) means "quiroga" and "Quiroga." hash the same, which is what you want when someone types an answer at 1am.
I'll be honest about the limits, because this is where posts like this usually oversell. A name has very little entropy, so anyone who wants to can brute-force those hashes. The solution files that ship with each case are sealed, but that's obfuscation, not encryption: an offline game has to carry its own key, and whoever reads the source will find it. This is a single-player game, not an exam. The point is that the answer isn't lying around in a file you open out of curiosity, not to stop a determined person from cheating at a game they paid for.
The whole engine ships to the browser. Measured on the live build: 7.3 MB for the gzipped engine and about 650 KB for the Parquet extension, so a bit over 8 MB before the first query runs. On a good connection that's a couple of seconds. On slow mobile data it can take most of a minute.
Three things make it bearable. The title screen needs only a small part of that, so the game opens right away. The case file opens without waiting for the engine, and it opens on text (a briefing, a list of suspects), so there's something to read while the rest arrives. And the download starts before the player asks for it.
What you get for that weight is real SQL. Window functions, CTEs, QUALIFY, Parquet read directly, real timestamp semantics. When the game teaches that BETWEEN on two dates quietly drops most of the second day, it's because the engine does exactly that, not because I wrote a lesson about it.
This one I learned from a player. They got stuck filtering by hour and wrote:
WHERE entry_time > '21:00'DuckDB says:
Conversion Error: invalid timestamp field format: "21:00",
expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH[:MM[:SS]]| ZONE])My game translated that into something friendlier about the format being year-month-day and "the day doesn't go first". Great advice for a mistake they hadn't made. They hadn't put the day last. They hadn't put a day at all.
Now the game recognizes that case and says that a TIMESTAMP holds a date and a time, and offers both ways out: write the full date, or use hour(entry_time) >= 21. It also links straight to the field manual entry.
Which exposed the other half of the bug: that manual entry only unlocked after you finished the case it belonged to. The help arrived right after you needed it. Now it unlocks when you start the case.
One question in a comment thread, two fixes. That's a better rate than any of my own playtesting.
The game is Caso Abierto. The first case runs in your browser, free, no signup. If you want to watch the proof rule reject you, SELECT 'anyone', '22:20' is still the fastest way.
The first case runs free in your browser, no signup.
Play the first case free →