Making players prove it: validating that a SQL query derives an answer

christianvadillo — Wed, 16 Sep 2026 16:02:36 +0000

I like detective stories and I wanted to get better at SQL, and at some point it 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.

Here's the shape of it. Case 2 gives you a witness who needs new glasses and five details: the man was at least 1.80 m, light eyes, an anchor tattoo on the forearm, worked the crowbar left-handed, no glasses. Then it hands you the municipal census, fifty-one rows, and asks which resident meets all five at once.

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 version that didn't work

The first implementation was the obvious one. You type the name, the engine compares it against the expected answer, done. It worked, and it was boring, because the SQL was optional. You could stare at twelve rows, spot the name with your eyes, type it, and the game would congratulate you on your query skills.

So I added the proof: alongside the accusation you submit the query that supports it. The game runs it and checks that the answer appears in the result.

That lasted until I tried to break it:

SELECT 'Ada Ferrán' AS name, '22:20' AS time

Accepted. The one gesture of inference the game demanded was, in practice, optional — and now it even had a rubber stamp.

Two rules

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 a query that reads some unrelated table doesn't count either.

Two: the query can't contain, written by hand, the value it claims to demonstrate. Filtering by the answer is assuming the conclusion. If the culprit is Ada Ferrán, then WHERE name = 'Ada Ferrán' proves nothing: you already knew.

The second rule is where it gets interesting.

The part I got wrong twice

First attempt: collect every literal in the query, compare against 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 legitimate 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 unglamorous: look for numbers outside the strings, since the 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 a thread, and sooner or later a case will ask for something you legitimately learned three cases ago — an account number, a card. 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.

Both of these came from watching the thing reject queries it shouldn't.

Doing it in the browser without shipping the answer

The game also 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 in the JavaScript?

The catalog doesn't carry the answers. It carries plain 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 (!hashes.has(clave)) hashes.set(clave, await sha256(clave));
  if (aceptados.has(hashes.get(clave))) return { ok: false, motivo: "literal" };
}

Normalizing first (lowercase, strip accents and punctuation) means "ada ferran" and "Ada Ferrán" hash the same, which is what you want when someone types an answer at 1am without accents.

I'll be honest about the limits, because this is where blog posts usually oversell: a name is low-entropy, so the hashes are brute-forceable by anyone who wants to, and the full solution file for each case is served as a static asset anyway. This is a single-player game, not an exam. The hashes exist so the answer isn't lying in plain sight in a file anyone opens out of curiosity, not to stop a determined person from cheating at a game they paid for.

What DuckDB-WASM costs, honestly

The whole engine ships to the browser. Measured on the deployed build: about 8.8 MB before you can run your first query, of which 7.3 MB is the gzipped wasm and 626 KB the Parquet extension. On a good connection that's a couple of seconds. On slow mobile data it's closer to 45.

Three things make it survivable. The title screen needs only ~650 KB, so the game opens immediately. The case file opens without waiting for the engine, and it opens onto 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 worth saying plainly: real SQL. Window functions, CTEs, QUALIFY, Parquet read directly, actual timestamp semantics. When the game teaches that BETWEEN on two dates silently drops most of the second day, it's because the engine does exactly that.

Error messages are game design

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". Which is excellent 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 specifically 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. And it links straight to the field manual entry.

Which exposed the other half of the bug: that manual entry only unlocked after finishing the case it belonged to. The reference arrived right after you needed it. It now unlocks when you start the case.

They asked one question in a comment thread and it produced 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 see the proof rule reject you, SELECT 'Ada Ferrán', '22:20' is still the fastest way.