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.

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

Five phases of ten. Done: the Laravel 13 skeleton on PostgreSQL and Redis in Docker with continuous integration, Sanctum authentication with its roles and policies, the content catalogue — exams, topics, sources, questions, options and tags — with one test per invariant checking that it actually fires, the answer log and the review scheduler. 123 tests, 91 of them against a real PostgreSQL, and PHPStan at level 6 with no errors.

Not done: the study interface, study sessions, timed mock exams and the editorial review panel. It is not deployed and cannot be visited.

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.