Deadlock Holmes

A noir detective game where you catch the killer by writing SQL. Each case is a working SQLite database of suspects, alibis and timelines. No MCQs. You type the query yourself, and solve the case!

Comment

Inspiration

I have started a lot of SQL courses. I have never finished one. They all work the same way: a short lesson, a table with six rows, type a SELECT, get a green tick. Nothing is at stake, so I always drift off.

Detective stories have what those courses are missing. A case is a pile of facts. On their own they mean nothing. You have to work out which fact connects to which. That is a join.

So we built a murder mystery instead of a tutorial, and made the evidence room a database.

What it does

Deadlock Holmes has fifteen cases in three volumes.

Every case is its own SQLite database. One is a hospital ward and its shift rosters. One is a passenger manifest. One is a property deed. Each case holds eight to fifteen people, and every one of them looks worth checking.

You get a case brief, the schema, and a query editor. That is the whole interface. There is no list of names to tap. To find the killer you write SQL and read the rows that come back.

The suspect list is too long to solve by eye, so running SELECT * and scrolling will not get you there. Each innocent person is cleared by a different fact. One good WHERE clause will not rule them all out.

Every case has four hints. The first is free and sits in the brief. The other three cost something and unlock one at a time. None of them names the killer.

Accuse the wrong person and you lose XP.

Cases get harder inside a volume. You start with filters, then joins, then subqueries and aggregates. The next volume starts easy again.

Eleven of the fifteen cases are free.

How we built it

Flutter, with SQLite running inside the app. No server, no sign-up, and it works offline.

Case content is data, not code. One case is one JSON file plus one line in an index. We have never opened a Dart file to add a case. The paywall works the same way: which cases are locked is a field in that index, so we can change pricing without shipping an update.

Purchases run through RevenueCat. Three parts of the app need to know what you own: the case list, a link that opens a case directly, and the screen that picks up where you stopped. All three read the same answer instead of working it out for themselves.

OneSignal brings people back. Leave a case open and go quiet and you get a short run of messages that name the case you left, rather than telling you to come and play.

Play Games Services covers what happens outside one phone: ten achievements, a leaderboard on total XP, and cloud save that carries your progress, your notes and your query drafts to another device. Signing in is optional. The whole game works without it.

We also removed the system keyboard. All typing goes through a keyboard we wrote.

The reason is simple. On a normal phone keyboard, quotes, underscores and brackets are two taps away. That hurts when every character you type is SQL. Our keyboard puts them on the first face. It also suggests table and column names from the case you are in, and long-pressing a SQL key explains what it does without sending you off to a help page.

Landscape works properly too. Your query sits on one side and your results on the other.

One test runs over every case in the game. It runs the solution query, checks it returns exactly one row, and checks that row is the right suspect. What that test cannot tell us is whether the case is a good puzzle. More on that below.

Challenges we ran into

The paywall almost broke progression.

Cases unlock in order. The paid cases sit in the middle of the list, not at the end. Under a simple rule, one locked case would shut every case behind it. A player who had not paid would hit a wall far bigger than the thing we were selling.

Unlocking now works one volume at a time and skips over locked cases. The gate is the last case you were actually able to open. Every volume starts with a free case. A case you have already solved can never lock again.

The custom keyboard also cost us things we had not thought about. There is no dictation, no autocorrect and no swipe typing anywhere in the app. That includes the notes tab, where you are writing English rather than SQL. We would still build it the same way, but it is a real loss and we are not going to pretend otherwise.

Accomplishments that we're proud of

Fifteen cases where the puzzle and the story were written together rather than one after the other. The database is arranged so the clue you need is one join away. The brief mentions that clue early, in a way that does not look important yet.

We are also happy with how the game looks. It is a detective's desk: wood, cork, torn paper, wax seals, pushpins, red string, stamps that sit slightly crooked. The screens are built out of those objects, not out of standard widgets in a dark colour scheme.

What we learned

Our test proves a case has an answer. It cannot prove the case is a good puzzle. We learned that from case 3.

Case 3 passed every check and was still broken in two ways. You could get the answer by reading one text column, with no join at all. And one of its decoys had stopped ruling anybody out, so the real suspect list was shorter than it looked. A test that only asks "does the query return the right row" sees neither problem.

Now we audit a case as its own job, after it is written. Almost every real bug in this project has come out of that audit rather than out of the writing.

What's next

iOS. Then more volumes. After that, case packs downloaded from a server instead of shipped inside the app. The content format was built for that from early on.

Built With

+ 18 more
Share this project:

Updates

Submission history