OpoStudy — Spaced repetition for public exams

Problem

Preparing for a civil-service exam means holding a lot of material in your head for months. A PDF test does not know what you got wrong: you drill what you already know and never see again the thing you missed three weeks ago, which is exactly what you have forgotten.

And generic quiz apps present a question taken from the official gazette the same way as an invented one. Studying on wrong content is worse than not studying: it fixes the error in place and you find out at the exam.

Decision

Write the design before the code. Seven decision records and five architecture documents — data model, API, algorithm, deployment — are dated before the first migration: some 3,600 lines settling which entities exist and why the relationships are what they are.

Three modelling decisions came out of that, and all three can be argued with. First: “subtopic” never became a table. It is the same entity as “topic”, with the same columns and the same relationships, and the third level that official syllabuses have would have called for a third table. A tree with parent_id takes arbitrary depth; the price is that walking it in SQL is clumsier than a join.

Second: the user-progress table was not created. It is an aggregate table, which is to say a cache, and a cache with no measured performance problem adds a second source of truth that can drift from the first. The numbers do not ask for it: two hundred questions a day is around 73,000 rows per user per year, and grouping that with an index takes milliseconds. There is a written threshold for revisiting the decision — 100 ms at the 95th percentile of the dashboard query — instead of leaving it open.

Third: a topic’s number belongs to the relationship, not to the topic. “Criminal law” is Topic 3 in the Mossos syllabus and Topic 2 in the local police one. If the number were a column on the topic you would have to pick one, wrong in every syllabus but one, or duplicate the topic and every question hanging off it. The syllabus code lives in the pivot table.

Implementation

The invariants live in the engine, not in application code. A rule that exists only in the application is bypassed by a bulk import, by a seeder, or by a console session in production.

A partial unique index on answer_options (question_id) where is_correct makes it impossible for a question to have two correct answers. That corrupt state has no symptom: the application would mark answers wrong in silence and skew the statistics of whoever is studying, and it is the data failure that costs the most to detect after the fact.

A CHECK constraint blocks publishing a question generated by a language model with no reviewer assigned. That turns what was an intention written in a decision record into a guarantee a job with a logic bug cannot step around.

Marking happens on the server, and the correct answer never leaves it before you answer. There are two resource classes — one for answering, one for reviewing — rather than one with an “include the solution” flag, because that flag eventually gets passed wrong from somewhere and the failure would be silent: nobody sees an extra field of JSON, they see an application that works. Eleven tests scan the whole response body for the solution, not one particular key.

An attempt is a fact that happened: it has no updated_at, and it stores whether the answer was right instead of deriving it on read. Deriving it would mean that fixing a badly worded question retroactively changes the past of everyone who already answered it, and with it the statistics telling them whether they are ready.

The options are shuffled stably per user and question. If whoever writes the content — or the model that generates it — tends to put the correct answer first, returning them ordered by identifier gives it away without any response containing the solution; and if the order changed on every reload, the option someone was halfway through reading would jump.

The spaced-repetition algorithm is written as a pure function: no database underneath, no dependency container and no clock of its own — the instant is a parameter and the randomiser comes through the constructor. That is what makes it possible to simulate ten thousand chained reviews and check the invariants after every one. With the same logic inside a controller, that test would be ten thousand HTTP requests and nobody would write it.

Writing that simulation corrected the design document in two places. The document justified the random jitter on due dates by saying that without it the daily load would be “zero for six days and two hundred on the seventh”. Measured, the worst spike goes from 1.91 to 1.62 times the median of its neighbourhood: the jitter helps, but the argument assumed that questions introduced on the same day get the same sequence of grades, and they do not. It stays, because it helps and costs nothing, but the strong claim was not true and is corrected with the numbers.

The second correction was to the test itself. Its first version compared each day against the median of all one hundred and eighty and reported a sixfold spike that did not exist: the load decays — the corpus stops growing while the intervals keep stretching — so it was measuring the slope, not the spikes. A threshold tuned to that badly framed measurement would have been loose enough that the test never caught anything again.

A study session mixes four sources — due reviews, weak topics, unseen questions and random ones — and the decision worth defending there is that the mix is a request, not a guarantee. Someone starting out has no due reviews and no weak topics, because they have answered nothing: applying the percentages literally would hand them seven questions out of the thirty they asked for. Whatever one source cannot cover passes to the next by priority.

And the split cannot be done by counting first and picking afterwards, which is the obvious route. The sources overlap: a question from a weak topic that has also never been seen counts as weak and as new at once, so adding the four counts yields more questions than exist, and the split hands out slots that can only be filled by repeating. It walks the sources once, in order, discarding what is already chosen.

The design stored the requested mix so that results could be compared across mixes months later. That did not work: you knew you had asked for 40% reviews, not whether there were any. It now also stores which source each question came from, which is what makes the original reason for storing the mix checkable.

The test suite runs against a real PostgreSQL 17 and not on in-memory SQLite, precisely because SQLite has neither of those two capabilities. There, the test asserting that a question cannot have two correct answers would pass green without checking anything — and a test that gives confidence while proving nothing is worse than no test.

Where it stands

All ten phases of the roadmap are done. Back end: catalogue, spaced repetition, study sessions with a configurable mix, timed mock exams with negative marking, administration with an editorial review flow, AI generation behind a provider interface, and observability. A Next.js interface to study, sit exams, see your progress and review content. 285 tests with 139,000 assertions, 195 of them against a real PostgreSQL, and PHPStan at level 6 with no errors.

Deploying it surfaced six failures that neither the 285 tests nor a local build could have found: service replicas incompatible with the fixed container name the orchestrator assigns; bind mounts of repository files that Docker turns into empty directories when the source does not exist; an nginx check that resolves DNS which does not exist yet; the hosts file being read-only during the build; a domain variable the orchestrator does not read but generates; and a seeder that created a test user with a known address in production, which only failed by accident. This is the class of failure that does not show up without deploying for real.

What is still missing: content. Not a single question. The seeder does not invent them on purpose, so the application works and is not yet usable for studying. Nor is there an AI provider configured: the generation flow is complete and the default generator fails saying what is missing, rather than returning invented questions that would land in the review queue indistinguishable from real ones.

The seeder does not seed questions either, and that is deliberate: a question made up to fill space is wrong content that looks right, which is the exact thing this project exists to avoid. The topics and the five statutes are real, and they are seeded as unverified, because “unverified” and “checked and correct” are not the same thing.