SAT Teachers
Platform for Ascend Now's 1:1 SAT tutoring — a tagged question bank, a live teacher-driven
question loop during Zoom sessions, and an evidence-backed session report for the parent.
Running it
npm install
cp apps/web/.env.example apps/web/.env.local # fill in your Supabase keys
npm run dev # http://localhost:5173
npm run build type-checks and builds. npm run typecheck on its own is faster.
What works today
| |
|---|
| About the SAT | A page in the app, before any test: the day, how the adaptive modules work, and what English and Math each ask — for a student new to the SAT, ahead of their diagnostic |
| Signup | Teachers only, and a new teacher is in at once — nobody approves it; the admins are emailed and see it on their dashboard |
| Login | Email + password, for teachers |
| The admin portal | One seat that sees every teacher, every session, every form and every report — and can do to any session what its teacher can: run it, write it up, generate the report and publish it |
| Suspend | An admin closes an account, or reopens it — and a closed account cannot sign in |
| Question bank | Teachers write and correct MCQs: passage or figure, question, up to 4 options, key, explanation |
| Three tests | English is easy, medium and hard — twenty questions each, under Questions, read as printed |
| Maths, three too | Mathematics has the same three tests, sixty questions filed into them by 0041, so a maths session runs |
| Where it goes | A question is written inside a test, in one call, and takes that test's subject and level |
| Difficulty | Easy / medium / hard, and every question says why it sits at that level |
| Sections | Subject, the four SAT sections the teachers assess against, and the skill within each |
| Sessions | Schedule with a student and a time. That is all — nothing to build beforehand |
| The roster | Add a student from the New session form: first name, last name, and their PC, chosen from the list. No sign-up |
| PCs | Accounts an admin adds, emailed a link to choose their password. A student's PC is chosen at their first booking and cannot be skipped; the PC reads that student's sessions and reports, and is emailed every report as a PDF with a link to the session |
| The link | Every session carries one. Send it and the student is in — no account, no login |
| The list | One table, searched and filtered: student, PC, id, title, status, level, subject |
| The level | The session starts on easy and the teacher moves it. The student is never told which test they are on |
| Choosing the question | The console names the question coming next, and the teacher can put any question from any of the three tests up instead — now, or after the one on screen |
| The console | Everything the student's screen can do: open the test, see the live question, answer for them, hand it in |
| Exam screen | One question at a time, its own clock running, stimulus left and question right |
| Live loop | Watch each answer land with its time and confidence, reveal, diagnose in one tap |
| Speed | Every answer is timed from first view to submit, and measured against a per-question target |
| Report | Score, per-skill and per-section breakdown, pace, and every miss with what both people said |
| Transcript | Drop in the Fathom recording; it lines up against the questions and is read back as findings |
| Loaded bank | 60 items with passages, keys, sections and difficulty, and every one of them in a test |
| Branding | Logo and colour tokens taken from the operations dashboard, so both apps look like one product |
| What a session is for | A diagnostic or a targeted test (0059), each with its own tab under Sessions and its own booking form. A student's first diagnostic in a subject is their baseline on every skill |
| Targeted tests | A short set on one skill, built by the teacher, sat alone on the link against the clock, and read back with its own report (0061, 0062) |
| Progress | Each skill's line from the baseline through every targeted test, with the sessions between and a verdict that is computed, never decided (0063) |
| Practice pool | Questions by skill, outside the three tests, that a targeted test draws on and a lesson never shows (0060) |
English is three tests
Easy, medium and hard. Sixty published items, twenty in each, every one with its passage, four
options, the correct option, an explanation, its SAT section, its skill from the teachers'
evaluation grid, and a sentence saying why it sits at that level.
| Test | Items | Source refs | Migration |
|---|
| English — Easy | 20 | ENG-DIAG-T4-M1-Q01 … Q25 | 0009, filed by 0026 |
| English — Medium | 20 | ENG-DIAG-MEDIUM-Q01 … Q27 | 0026 |
| English — Hard | 20 | ENG-DIAG-T4-M2-Q02 … Q26 | 0009, filed by 0026 |
The grouping is the teachers' own, taken from the level document they marked up. It replaced the
per-item difficulty labels the items were transcribed with, which disagreed with the teachers'
sorting on about half of them — so questions.difficulty is now the test an item is in, and
questions.difficulty_rationale explains that placement item by item. It is not decoration: it is
the sentence a teacher reads when deciding whether to move a student up.
The item numbers are each test's own and are not contiguous — the medium test runs 1, 2, 3, 6, 7,
… — because renumbering them would make a teacher's "look at 19" mean two different questions.
The bank used to hold twenty-six items besides: the in-class Reading and Writing – 25Q
diagnostic (0008) and one Test 4 item the level document does not use. 0029 retired them,
because an item no session can ask is not stock and totalling it with the sixty made the bank look
half again as deep as it is. 0040 deletes them, with one exception it makes on purpose —
ENG-DIAG-T4-M2-Q01 is the first question of the 7 August recorded session (0012), and
session_items points at questions without a cascade, so deleting it would not tidy a report, it
would tear a question out of one. It stays, and it stays retired. Read on the in-class paper's
answer key, which came with seven printed answers that disagreed with their own passage, in
docs/reference/english-diagnostic-key-review.md
— the review stands even though the items are gone.
Reading one as a paper
/questions opens on the three. Opening one prints it: the
directions block at the top, each passage set once above the questions that hang off it, the
choices as the paper's own A)–D) run, and the test's own numbering. The answers are a toggle
and start hidden — a key on screen while a teacher is talking a student through a question is a
key read out by accident. Under each answer is why the item is at this level. Print gives them
the paper on paper.
The tabs and tables the papers print are stored as text in the bank, so both the paper view and
the student's screen parse a stored passage back into what the paper set: paragraphs, the
"Text 1"/"Text 2" headings of a paired-text item, and a real table for the two chart items. The
student sees the same passage, the same stem and the same four choices the paper prints —
apps/web/src/lib/paper.ts is the one place that decides what that looks like.
question_sets is the test — the same object a session loads a level from, so a test a teacher
reads is the test their student sits. Migration 0026 registers the three with their level,
their directions and their order.
Every item is fully labelled: section, skill (all eleven of the grid's Skill Focus rows) and
level. A skill belongs to exactly one section and the database checks the pair, so an item cannot
be mis-filed. Nothing was left blank for the teachers to fill in — they correct a label in the
bank instead.
Some items ask about "the underlined sentence", so the bank stores the underlined span alongside
the passage (questions.passage_underline) and renders it marked wherever the passage appears.
Two items are built on a chart and a table; both are transcribed into the passage as text rows,
since the bank is text-only. House content like this has no author — questions.created_by is
null — and any teacher may correct it.
Writing and correcting a question
A question is written into a test, or it is not written. /questions/new?paper=<id> is the
only way in; reaching /questions/new without one lists the tests instead of opening the form.
create_question_in_set writes the question, its options, its key and its place in the test in
a single transaction (0040), so there is no window in which a question exists and is filed
nowhere. The test decides the subject and the level, so neither is asked for: an item in the easy
test is easy, which is the rule 0026 set and the one thing that keeps the bank's counts and the
tests from disagreeing.
They did disagree. The bank page read 61 questions in use · 21 medium while the medium test
held twenty, because writing and filing were two calls and the bank page's own Add question
button had no test to file into — it made a question that was counted in the headline and
displayed on no screen in the product. Every count on the bank page and the dashboard is now
summed from the tests themselves, so a number that moves has a row you can open; anything the
bank still holds outside a test is named at the top of the page rather than folded into a total.
/questions/:id/edit corrects one — the same form, and
update_question mirrors create_question: one call, one transaction. Options are replaced
wholesale rather than diffed, because the key points at a label and a diff could leave it
pointing at an option that had moved underneath it. RLS decides whose questions may be rewritten:
a teacher's own, and house content, which any teacher may correct.
A question can carry a figure — a diagram or a chart, for the maths items that are a picture
rather than a paragraph. It is uploaded to the question-images bucket and the row keeps its
URL. That bucket is public, which is a real decision: a student has to load the image the moment
the question is published to them, and signing every URL would mean a round trip per render on a
screen that must not stall mid-test. Paths are random UUIDs, so an image is unlisted rather
than secret — the standing of an unlisted document. No answer key is in the picture. Writing to
the bucket is teacher-only.
There are three tests, and that is the number
question_sets.level is what makes one of them a level: easy, medium or hard, unique per
subject among the active sets, and the column a session looks a test up by. Matching on a title
would have been a bug waiting for a rename.
Everything else that was ever a set is deactivated rather than deleted — the three source papers
the bank was loaded from, and any test a teacher assembled by hand under the old flow. Their rows
and their items are still there, so a report of a session that ran off one still resolves; what
they are not is runnable, because a session runs a level.
Add question on an open test — or on its row in the bank — arrives at
/questions/new?paper=<id> and files it onto the end. Every question carries an Edit link, so
a typo is fixed where you found it, and an edit cannot move a question out of step with the test
it is printed in: the level shown is the test's and is read-only.
Mathematics has its three tests as of 0040 and its sixty questions as of 0041 — twenty
easy, twenty medium, twenty hard, sorted by the teachers before they reached us and loaded as
house content by source_ref, nine of them with a figure that ships with the app. Nothing else
was needed to make them work: load_session_level has looked a test up by the session's subject
as well as its level since 0027. The New session form gates the subject on the bank rather than
on English being named in the code, so filling the tests was the whole of what it took — and
0042 finished the other end of it, the diagnostic form a mathematics session is written up on.
Identity codes
BATO26-1 — three letters of the given name, one of the surname, the two-digit year of
joining, then a serial number for that role, starting at 1 and unbounded.
The trailing number identifies the person, so it is shared across all names of that role rather
than restarting per name. There is exactly one -1 for teachers and one -1 for students:
| Order | Role | Name | Code |
|---|
| 1st teacher | teacher | Malya Rao | MALR26-1 |
| 2nd teacher | teacher | Priya Sharma | PRIS26-2 |
| 1st student | student | BATU Ozcelik | BATO26-1 |
| 2nd student | student | Jo Kim | JOXK26-2 |
| 3rd student | student | Madonna | MADO26-3 |
| 4th student | student | Batu Ozdemir | BATO26-4 |
Short or single names still produce the same shape — JOXK26, MADO26 — padding with the
fourth letter of the given name, then X.
The number does not reset each year, so it stays unique for the life of the account. Two people
can still compute the same prefix (a teacher Test and a student test joining the same year
both give TEST26); when that happens the code takes the next number for its role rather than
failing the signup.
How a session runs
There is nothing to prepare.
The teacher creates a session with a student and a time, and that is the whole of their side
of it. No paper to build, nothing to stage, nothing to hand over during the lesson.
The student is a roster row, not an account. If the teacher already has them, they pick them
by name, id or PC. If not, they type first name and last name into the same form, choose their PC
from the list, and the student is created as the session is — create_student (0032, 0055),
teacher-only. There is no student sign-up any more, and profiles.id no longer references
auth.users, because a roster student has no auth user to point at. No session is booked without
the student's PC — see PCs.
The session is a link. Every session carries a 64-character token and the form hands it back
the moment the session exists: /s/<token>. Opening it puts the student straight into their own
session with nothing to sign into — the same lobby, the same questions, the same clock. The token
functions (0033) take the token, find the one session it names, and do exactly what that
session's student could already do; the payload is built server-side and never carries the answer
key, the token itself, or a question that is still staged. Since 0064 it is a fixed list of
fields — the ones the student's screen reads — rather than whole rows, so the session's level, the
teacher's notes and a question's difficulty, rationale, source and skill never reach the browser,
and a column added later stays off the link until somebody names it (link_payload.sql). Students who already have an account
still sign in and still open their sessions at /exam/:id; the link is a second door, not a
replacement for the first.
The student opens the session once its time has passed and waits for their teacher: nothing
is on their screen until the teacher shows a question (0057). One question is on screen at a
time, each with its own clock. They answer, press Submit answer, and wait again — there is no
Next button and no way to move on alone. Nothing on their screen says which test a question came
from — no level in the header, no level switch, no "of 20" — and the questions are numbered 1, 2, 3
across the whole session. A student who could watch "easy" turn into "hard" started treating a diagnostic as a final
exam, and which test they are on is the teacher's judgement about them, not something they can act
on.
The teacher shows every question (0047, 0057). Choose a question opens the three
tests side by side: every question by the number the printed test gives it, what has happened to
each in this session, and the chosen one in full with why it sits at its level. Show it to them
puts it on the student's screen; with a question still up it reads Show now, and the one they
were on is set aside. Nothing is queued behind it: starting the test puts nothing up, and neither
does an answer, so the student only ever sees a question the teacher chose. Until 0057 the test
ran itself — the easy test loaded at the start and every answer brought up the next question in a
queue — and the teacher reached in when they wanted something else; the lessons are not run that
way, so the queue is gone.
The level moves when it is wrong, and only the teacher moves it: the console's three buttons, or
choosing a question from another test. A level button puts up the first question of that test the
student has not had in front of them, and nothing after it. The
question that was on screen is set aside — voided, not counted against them — and a question
already asked is never asked again, even coming back down. That holds for choosing too: a question
the student has answered, or had in front of them and had set aside, cannot be chosen again.
Easy → medium → hard is the path; the other direction works too, because "drop one level — rebuild
fluency before speed" is a real instruction and had nowhere to be acted on.
The student had a switch of their own until 0047, because the level used to be decided out loud
and clicked by whoever was nearer a mouse. It went with the level on their screen, and so did the
database's half of it: set_session_level answers the session's teacher only, and the link's own
set_level_by_token is dropped rather than left callable.
When the student's screen is not working, the teacher's is. A phone, a school network, a
browser that will not go full screen, a Zoom share that never starts — any of them used to leave
the teacher blind and the session stuck, because starting the test, answering, moving level and
handing in were all things only the student could do. The console does all four now (0034). It
shows the question the student is on in full — stimulus, stem, all four choices — and Answer
for the student enters what they said out loud. That is not a second kind of answer: it goes on
the student's own item, is graded against the same key and stops the same clock, because the
report reads one data set and a second kind of answer in it would be a lie about the lesson. The
next question is still the teacher's to show.
Routes. Every screen is a place: /questions (the bank, opening on the three tests),
/tests/:id (read one), /sessions (the table), /sessions/:id (the console). There is no
/tests list and no Tests nav item — Questions already opens on that list, and a second entry
pointing at the same three rows was a menu item that told you nothing. The exam is /exam/:id
for a signed-in student and /s/:token for one on a link, both deliberately outside the app
shell: a student sitting a test should see the test and nothing else. /s/ is matched before
anything asks who is signed in, because the answer is nobody.
Times are UTC, everywhere and always — written on the schedule form, printed on every
session card, and said out loud in the text (31 Aug 2026, 14:30 UTC). A teacher in Singapore
and a student in Dubai have to mean the same moment by "half four", and rendering each browser's
own zone meant they did not. apps/web/src/lib/time.ts is the only place that formats one.
When the results are published the student gets the whole test back, not a list of letters:
every question as they met it, their answer and the right one marked on the choices, and the
explanation underneath. The key is not in a student's reach — question_keys is teacher-only —
so it comes from what the reveal copied onto their own item row.
The student sees the session on their list with a countdown. Once the scheduled time passes the
Start button turns on — no one has to let them in.
The test runs full screen, asked for inside the click that starts it — the only moment a browser
grants it. Leaving full screen is not blocked (no browser allows that, and none should), so it is
treated as what it is: the screen asks them to come back or to finish.
Nor can they wander off: while the test is live — between questions too — the browser's back
button and a refresh are both caught, and leaving is a decision the screen asks about first. Saying yes submits the test
as it stands — finish_session_as_student completes the session and voids every question they
never answered, including the one on screen. A test you can leave and come back to is not a test,
and the per-question clock would mean nothing.
Afterwards the teacher presses Publish results once and every answered question is
revealed to the student. Whether they learn how they did is one decision about the session, not
twenty decisions about twenty questions. It publishes no report: that is a separate, deliberate
step below the board, and it cannot happen before the diagnostic form is in.
Diagnoses are still per question — that is the teacher's judgement, and it is what the report is
built out of — but they can be tapped as soon as an answer lands rather than only after a reveal.
The board is grouped by test, not by level-the-session-happens-to-be-on. A student who did
six easy questions and then twenty medium ones sat two tests, and both get their own heading with
their own score. Questions the student never reached are behind a switch rather than in the list
— fourteen "not attempted" rows bury the six that carry the lesson — but a test that was opened
and produced no answers still gets its heading and says so, which is the case the grouping was
built for: without it, a level the student was moved off before answering anything vanished from
the console entirely.
One question is in front of the student at a time and it is the server that holds that line:
only the current item is published, and a staged row is invisible under RLS. Since 0057
nothing is staged at all — the teacher's choice inserts one row and publishes it, and
submit_answer publishes nothing — so the clock on question 3 cannot be spent reading question 4
because question 4 does not exist until the teacher shows it. record_answer checks the question
is still open in the same statement that answers it, so a teacher setting a question aside at the
instant the student submits cannot leave two questions open (0047 has the race, and the reason
lock_open_item looks twice).
Speed
Every answer is timed server-side: session_items.first_viewed_at (set when the question is
actually on the student's screen, not when it was published) to decided_at — the moment the
student picks an answer. Not the moment they have also said how sure they are (0050): the
confidence is a question about the answer, asked once there is one, and the seconds spent on it
are not seconds spent answering. Nor the seconds after, finding the Next button. Both were
landing in the number the report calls pace. Stored as session_item_assessments.elapsed_seconds;
the student watches the same interval stop on the question itself, and the teacher on the
console. A change of mind afterwards does not restart it. Each question carries a target_seconds benchmark, so
the report can separate wrong from wrong in nineteen seconds — those need different fixes.
The report
/sessions/:id/report. Score, per-skill and per-section breakdown weakest first, pace against
target, the teacher's diagnoses, and every miss with the student's own reasoning and the teacher's
note beside it. Everything is computed from the session's rows — nothing is stored and nothing is
written by hand, so the report cannot say something the session did not.
Save as PDF gives the parent's copy. The app furniture drops away — sidebar, back link,
buttons — and what is left is the report as it appears on screen, keeping its heading (student,
subject, date, teacher) and keeping cards off page breaks. There is no second document to hold in
step with the data: the PDF is the report.
The diagnostic form
/sessions/:id/diagnostic. A diagnostic finishes with no score and no report, and what the
teachers do next they already do on paper: they fill in the reflection grid while the
hour is still fresh, write what they made of it, and hand over the Fathom transcript. The report
is generated from those afterwards. This is that form, in the order they fill it.
diagnostic session ends
→ no score, no report
→ the console offers one thing: FILL THE DIAGNOSTIC FORM
→ teacher fills the reflection grid ✓/✗, strengths, gaps, next steps — per domain
→ teacher writes their comments
→ teacher uploads or pastes the Fathom transcript
→ the console shows the form back, and offers GENERATE REPORT
→ teacher presses it; the report is generated and stored
→ the report goes to the student and the parent
The console follows that order and offers nothing else. There is no Report button and no
Diagnostic form button sitting in the header from the moment a session is created — a button that
is there before there is anything behind it is a button that gets pressed at the wrong time. While
the test is running the console is the board. Once the session is over, a panel above it offers
the form — above, because writing it up is the work now and the answers are the reference, and a
teacher scrolling past twenty rows to find the button is being shown the wrong thing first. Once
the form is submitted, the same panel shows what was written — the grid, the comments, the
transcript — with Generate report underneath. generate_report refuses to run
before form_submitted_at is set, so the rule holds outside the browser too. Generating is not
sharing: the report stays a draft until it is published.
The grid on screen is the grid on the paper: the same six columns in the same order, four domain
rows, Domain and Skill Focus printed and the rest editable. Which four rows depends on the
subject — English is assessed against Information and Ideas, Craft and Structure, Expression of
Ideas and Standard English Conventions; mathematics against Algebra, Advanced Mathematics,
Problem-Solving and Data Analysis, and Geometry and Trigonometry. They are the same four sections
the questions are already tagged with, so the grid, the report's per-domain breakdown and the
question bank all group by one list rather than three. 0042 taught
session_domain_notes.domain the mathematics four: until it did, a mathematics session could not
be written up at all, because the only form Postgres would accept was one judging it on Words in
Context. Mathematics has no printed paper form yet, so its Next steps/Targets column arrives with
our wording rather than the teachers' — the one part of the grid that is not taken from theirs. Next steps/Targets arrives
prefilled with the form's own wording — a teacher who agrees with it should not have to retype
it to say so — and is theirs to rewrite. Student Performance offers a tick and a cross and
nothing else, because that is what the paper offers: it is one judgement the teacher signs about
the domain, not an arithmetic over answers nobody has marked yet. The paper's box is bigger than
a tick and teachers write in it, so there is a note under the mark — the one optional box on the
form.
Every field is required. A report generated from a form with two domains filled in reads as a
judgement about four. Trying to hand in an unfinished one marks the empty cells and says what is
missing by column rather than listing sixteen of them; submit_diagnostic_form checks the same
thing in Postgres, so "required" is a rule rather than a convention the browser keeps. A
part-filled form still saves as a draft — nobody types four domains of notes in one sitting.
What it writes: session_domain_notes gains performance, performance_note and targets
alongside the strengths and gaps it already held; session_reports takes the comments in
teacher_reflection and the two moments in form_submitted_at and generated_at; the transcript
goes where it always went. Nothing on the form computes, scores or concludes anything.
A form that has been handed in is changed by handing it in again. Its Save draft button goes,
because a draft saved over a handed-in form would skip the form's checks and leave the report
made from a form that no longer exists. Resubmit form moves form_submitted_at, and the page
it returns to — the console, or the admin's page for the session — reads the recording again and
generates the report on arrival, because the reading was taken against the form as it was
(below) and whoever handed it in again has already said the report is to be made from it.
The two recorded sessions
Two real diagnostics are in the database as data rather than as documents, so every screen in the
product has something true behind it.
| English — 7 August (0012, 0014) | Mathematics — 28 August (0043) |
|---|
| Questions | 18 of the 23 discussed | 7, the ones the teacher picked |
| Score | 13 of 18 | 5 of 7 |
| Level | one module | mixed: three easy, two medium, two hard |
| Write-up | grid, summary, report published | grid, comments, transcript, report not generated |
The mathematics one is the 46-minute recording of a grade 11 IB student sitting the SAT for the
first time: MATH-DIAG-AUG28-Q01–Q07, every answer she gave, the two times the teacher measured
out loud (16 seconds on the percentage question, 65 on the intersection one), her reasoning in her
own words and the teacher's note on each. The Fathom transcript is on the session and the times
are the recording's own, so the write-up page lines the two up at an offset of zero.
It stops where the teacher stopped. The diagnostic form is filled in and handed in —
form_submitted_at is set, all four mathematics domains carry a mark, strengths, gaps and next
steps, and the comments are there — and the report is not generated: generated_at is null
and the console offers the button, which is the state a teacher is in the evening after a session.
Three things in that migration are ours rather than the recording's, and it says so where they
appear: the distractors the student never names aloud, question 4's key (the teacher reads the
printed choices and says the answer is not among them — it is 403, and it was not), and the
elapsed times she did not measure, which are the gap between the turn a question opened on and the
turn the answer landed in, an upper bound rather than a stopwatch.
The seven are retired, like ENG-DIAG-T4-M2-Q01 before them. They are the questions one
teacher picked for one afternoon, they are in none of the three mathematics level tests, and no
future session should ask them — which is what retired means here (0029). The bank counts them
as retired rather than warning about seven strays it cannot explain, and nothing that reads a
session looks at a question's own status, so they stay readable on this session's board and in its
report for as long as it exists.
One judgement on the form is worth knowing about: Geometry and Trigonometry was never tested,
and the form offers a tick or a cross and nothing else. It carries a cross, and the note beside it
says the cross is the absence of evidence rather than a verdict on the student. The row's next
steps are to sit that paper before any more algebra.
Reading the recording
Between the form and the report there is one more button: Read the recording. The transcript is
cut into one window per question — arithmetic, from first_viewed_at plus the offset, never a
guess — and a model reads each window and records what it shows: how the student got to their
answer, what they misunderstood, a word they said they did not know, and everything the teacher
told them about that question, typed by what kind of feedback it was.
The rule is the one the rest of the report keeps. Every claim carries a quote, and a claim whose
quote is not verbatim in the recording is dropped rather than repaired — along with any claim
carrying a number its own quote does not contain, because figures come from the answer rows. The
checking happens in the edge function, not the browser, since a promise a client makes about itself
is not a promise. What survives is stored; what did not is stored too, because a rising drop rate is
how a broken prompt announces itself.
It reads who is speaking from what was said, not from the label. Fathom attributes a block of time
to one person and the other's words land at the end of it — across the two recordings we have, the
teacher's "Very good absolutely right, good job with the vocab questions" sits at the tail of a turn
labelled with the student's name, fourteen provable times and never once the other way round. The
deterministic reader believes the label and therefore reads the teacher's own explanations as the
student's reasoning. Where the model overrules a label the report says so on the line.
The report then puts the three side by side and never merges them: what the teacher wrote, word
for word; what the recording shows, each with its quote; and what the answers count. Every
piece of transcript evidence says whether it supports, complicates or adds to what the teacher
wrote, and the ones that complicate get their own section — that is the row where one of the two is
wrong and only the teacher can say which. Without it a teacher's own sentence, handed back in
different words, would read to a parent as a second independent finding.
A reading is optional. The form, the grid and the computed numbers are a complete report on their
own, and a model being down on a Thursday does not stop a teacher finishing their work. A reading
that is out of date is not optional to notice, and a reading goes out of date two ways (0051):
- the transcript changed after it — it quotes lines the recording no longer has;
- the form was handed in again after it — it filed its evidence as supporting, complicating
or adding to what the form said, and the form says something else now.
generate_report refuses either, the console and the admin's page read the recording again
first, and the report leaves a stale reading out rather than showing it as the recording's
findings. When the model will not answer, Generate without the recording still goes through:
it removes the stale reading, so the report says it has none. For any of this to work the
reading has to know when it was taken — it is stored by an upsert, which never moved its
created_at, so a reading taken again looked as old as the first one. A trigger dates it now.
The design, the measurements behind it, and what to watch before trusting it are in
docs/reference/context-extraction.md.
Writing it up from the recording
/sessions/:id/report/edit. The session finishes, the teacher drops in the Fathom transcript, and
the page does the part nobody wants to do by hand: it lines the recording up against the questions
and reads it back.
Alignment is a lookup, not a guess. Every question already knows when it was on the student's
screen, and every Fathom turn knows how many minutes into the recording it was said. The one
unknown is where question 1 sits in the recording — Fathom starts before the lesson does — so
there is one dial for that, and Find it sets it by trying every offset and keeping the one
that leaves the fewest questions with nobody talking about them.
Then it reads the conversation. The teacher asks for the reasoning on every question, including
the ones the student gets right, and that is the thing the answer rows cannot see: a tick is a tick
whether the student explained it or shrugged. So each question gets a verdict — explained it,
self-corrected, right but no reason given, misread the stem, talked out of the right one,
method held but the concept did not, no reasoning — together with who did the talking on it. A
miss the teacher explained is not the same finding as one the student worked and still lost.
Those become findings, per domain, offered as buttons under the two written columns of the grid.
Each one names the questions it came from and shows the student's own words underneath, so it can
be checked before it goes in. Clicking appends it; the teacher edits it into their own words or
ignores it and writes their own. Nothing is written into the report by the analysis, and the
sentences that reach a parent are the teacher's.
It is deterministic — no model, no network call — so the same transcript gives the same findings
twice and the whole thing is unit tested. Who is who in the recording is a control rather than an
inference, because Fathom labels turns with whatever people typed into Zoom and attributing the
teacher's explanation to the student would poison every finding under it.
docs/reference/transcript-analysis.md is the structure
in full.
The session workflow
teacher creates a session with a student and a time (nothing else to do)
→ the scheduled time passes
→ student opens the session themselves
→ the EASY test loads; question 1 is published to them
→ student crosses out options, answers, says how sure
→ Next → answering publishes the next question; repeat
→ teacher sees each answer, the eliminations, the time and the confidence
→ too easy? the teacher presses Medium on the console
the open question is set aside, the rest of easy is dropped,
medium's question 1 is published
→ about right? nothing to press. keep going.
→ wants a particular question? Choose a question → a test → a question
Show now: it replaces the one on their screen
Show next: it comes up when they answer that one
either way the test carries on from the question chosen
→ the student sees the question. never which test it came from.
→ teacher publishes the results (only now does the student learn them)
→ teacher taps one diagnosis chip per question; the system suggests the next move
→ teacher fills the diagnostic form: the grid, their comments, the transcript
The suggestion encodes what the teachers already do — escalate on solid reasoning, hold the
level on a lucky guess or a concept gap, drop a level when they ran out of time. It suggests;
it never moves anybody. The teacher's judgement is the product.
The admin portal
admin has been in the role enum since 0001 with nothing behind it. is_teacher() counted an
admin as staff, so an admin signing in got the teacher's app — and then every policy in the schema
asked teacher_id = auth.uid(), which is false for somebody who teaches nothing. An admin could
see the question bank and not one session, not one form and not one report. 0044 is the role.
Users in the sidebar opens it, and it is three screens plus a filter on a fourth:
| |
|---|
| Users | Teachers, students, PCs (added, sent a new link or suspended here, and changed on a student's row), and the queue of teacher accounts waiting to be verified |
| A teacher | Their sessions, gathered under the student they were with, and the control that suspends the account |
| A session | Complete: every question with its answer, time and diagnosis, the teacher's diagnostic form as they filled it in, the transcript and the model's reading of it, and the written summary — and the way into all of it: the console, the form, the transcript, generating, and the report's editor, where it is published (0050) |
| Sessions | The teacher's own list, with a teacher filter added for the admin, who is the only seat that sees more than one |
Sessions are listed in exactly one place. The portal opened with an overview that counted them
again on a page that is not the sessions list, which is two readings of the same rows that can
disagree — so that page is gone and Users counts only people.
A teacher's page is grouped by student rather than ordered by date, because nobody opens it asking
"what did she do on the 4th". They ask how this teacher is getting on with this student, and that
is a run of sessions with a trend down it, which a date-ordered list interleaves with three other
students.
Every figure is tallied from the rows on read — nothing is stored — which is the same rule the
session report is built on, for the same reason: a number nobody can open is a number nobody can
check. The arithmetic is in lib/admin.ts and tested there rather
than inside a component.
An admin can do to any session what its teacher can (0050). 0044 made the seat a reader,
and 0048 and 0049 opened a session's write-up to it one door at a time — the transcript, the
form, generating. The first admin to pick a session up went through it the way its teacher would,
and met "not your session" at every step that was not one of those doors: Publish results on
the console, and the reading of the recording behind Generate. An admin is who picks a session up
when its teacher cannot — off sick with a report due, a form handed in wrong, results nobody has
sent — and oversight that has to ask the teacher to press each button is not oversight. So:
- Every RPC that runs a session. They all open with
assert_session_teacher, and that gate
lets an active admin through for any session that exists: letting the student in early,
starting it, moving the level, choosing the question, answering for them, ending it, publishing
the results, the diagnoses, handing the form in, generating, publishing and unpublishing the
report. The console is the teacher's own screen, and it works from the admin's seat; it says
whose session it is.
- Every row a session writes — one admin policy per table, for every command, beside the
teacher's own:
sessions, session_items, session_item_assessments, session_transcripts,
session_domain_notes, session_reports.
- Publishing.
0049 had a trigger refusing an admin who changed a report's status; it is gone,
and the report editor offers an admin the Publish button it offers the teacher.
What the seat still cannot do is the same as what nobody can: write the model's reading of a
recording (the edge function stores it on the service role, after its quote check) or make anybody
an admin. set_profile_active — suspend and reopen — is admin-only and refuses the caller's own row,
because the last admin switching themselves off locks the role out of the product.
Because two seats can do all of this, the database records which one did:
session_reports.form_submitted_by and generated_by, and the transcript's uploaded_by and
created_at, which a trigger moves whenever its text changes. Whoever is reading is told when it
was the other seat — the teacher sees "Submitted … by an admin", "Generated … by an admin", "last
changed by an admin"; an admin sees the teacher's name — rather than a new timestamp with no name
on it.
The edge function that reads the recording checks for the session's teacher or is_admin(), and
reads the form under the session's own subject's four domains — it had the English four written
into it, so a mathematics form never reached the model and every domain finding on a mathematics
session was dropped.
How the portal is set
Four screens of tables, so the reading is the design. One box per thing and a heading over each:
the tables carry the border, the prose sits on the page, and .section-head does the separating —
which is why no admin screen reaches for an inline margin. The figures are set right, so a column
of them is read down rather than across. The counterparts under a count clip to one line, but the
+4 that says how many did not fit never does.
Two things the portal used to say twice. A session's level printed the badges and then said them
again underneath — Medium over Medium test — so the badges carry it now, on one line, with an
arrow where the lesson moved, that being the one thing the badges by themselves do not say. And
every row carried two filled pills, its status and its write-up stage, which reads as decoration
rather than as two different facts: the stage is a dot and the words now, hollow while it is still
somebody's to finish and solid once it is theirs no longer, so the backlog can be read down the
column without reading a word of it. The stage badge also used to say Form In Progress —
text-transform: capitalize sat on the colour rather than on the levels that wanted it, and
capitalised every word of whatever was put in one.
Adding a PC is folded away (.folded), because it is done once a term and the list under it is
what an admin opened the tab for; the tabs and the search box are one line rather than two boxes.
Off is suspended
0044 had one kind of off: is_active = false, which every policy reads, so the account lost
every row in the database. That is the right amount of data (none) and the wrong door — the
password still worked, so somebody you had removed could still sign in and click around an empty
copy of the product. 0045 made suspending ban the auth user too, and stamps suspended_at:
| is_active | suspended_at | Can sign in? |
|---|
| Active | true | null | Yes |
| Suspended | false | set | No. The auth user is banned and their open sessions are deleted |
0045 also had a third row — pending, a new teacher nobody had approved yet — and 0058 took it
away (below). Letting somebody back in is the same button that took them away.
A new teacher is in, and the admins are told
Signup is open to the internet, and since 0058 a teacher account is on from the moment it exists:
nobody approves it. 0044 had written every new teacher inactive until an admin let them in;
0058 writes them active, and lets in anybody who was still waiting when it ran (a suspended
teacher stays suspended).
So the admins are told instead of asked. A trigger on the new profile calls the
notify_new_teacher edge function, which re-reads the profile on the service role and emails
whichever admins have an address: who signed up, and a link to their page. The dashboard every
admin lands on lists the teacher accounts made in the last week, and Users marks them New. An
account nobody recognises is suspended from its page.
Three things the notice is careful about:
- A signup must never fail because of a mail.
pg_net queues the request rather than making
the HTTP call inside the transaction, and the whole thing is wrapped so that any failure at all
— no key, no URL, the function down — is a warning and a signup that still worked.
- No secret lives in the database. The call carries no token, because the function does not
need one: it takes an id and will only ever mail admins about a profile that really is a
teacher, not suspended, made within the last day. Guessing a uuid gets you a second copy of a
true notice.
- The URL is configuration, not schema. It differs per project, so it is a row in
app_config
(RLS on, no policies, so no client can read it) rather than a line in a migration.
What this trades away is worth saying plainly: a teacher reads every answer key in the bank,
every student on the roster with their PC, and can edit the house content everybody else's sessions
are built from — and anybody with an email address can now have that. The role is still coerced to
teacher or student, so a signup cannot make itself an admin or a PC. The first admin is minted
in the SQL editor by somebody who already owns the project — there is no RPC that grants the role,
because any such RPC is a rung on a ladder:
update profiles set role = 'admin', is_active = true where email = '[email protected]';
The rest of the pass over the schema, the grants, the edge function and what is committed — what
was found, what 0044 fixed (and 0058 reopened) and what is still open — is in
docs/reference/security-review.md.
PCs
A student's PC used to be a line of text typed into the booking form — a name that identified
somebody to the teachers and to nobody else. It is a person now (0054, 0055): somebody who signs
in, is chosen from a list, reads their students' sessions, and is sent every report.
admin adds a PC under Users → PCs first name, last name, email
→ the PC is emailed a link it opens a page with their address filled in
PC chooses a password on that page and is in: no confirmation email, no second step
teacher books a student's first session
→ chooses the student's PC from the list required: the form will not book without it
→ that PC is the student's from now on shown, fixed, on every later booking
report generated by the teacher or an admin, from the console
→ the PC is emailed the PDF and a link to the session, within seconds
→ the console says so "PC emailed — Priya Rao, 14:32 UTC", or why not
PC signs in or follows the link in the email
→ their students' sessions and reports read only; nobody else's
Adding one. Users → PCs → Add a PC. The manage_pc edge function checks the caller is an
admin, then makes the account in two steps, profile first (create_pc_profile, service role only)
and the sign-in second, under the same id. It has to be that way round: GoTrue writes a new user's
app_metadata in an UPDATE after the INSERT, so the signup trigger never sees a role there — a PC
made the other way round arrived as a student and spent a student's serial — and user_metadata
is whatever a signup says it is, so a role read from it is a role anybody could claim. The signup
trigger now leaves alone a profile that already exists. The sign-in is made confirmed, with a
password nobody is told.
Joining. The PC is emailed a link, /join#token=…, which opens a page with their address
already filled in and not editable; they choose a password and are signed straight in. Nothing asks
them to confirm the address afterwards — the link reaching it was the confirmation. The token is 32
random bytes, carried after the # so no server logs it; pc_invites keeps its SHA-256, never the
token. It works once and for a week, and a new one replaces the old. Opening the page spends nothing
(mail scanners open every link in a message before a person does); choosing the password spends it,
through the accept_pc_invite function, which a signed-out browser calls and which believes nothing
but the token. Users shows each PC as Invited, Link expired or Joined. Send the link again
on an unjoined PC's row makes a new one; on a PC who has joined it is Send a password link, the
same page with "choose a new password", and their old password works until they use it. If the mail
cannot go — no mail provider set up yet, or the server said no — the PC is still made and the link is
shown to the admin once, to send themselves. Suspend is the switch every account has
(0045); it also stops the emails and kills any link. A PC who wants to change their password
while signed in does it from Account (their name at the foot of the sidebar).
Choosing one. The New session form has a PC field, and nothing books until it is filled.
A new student is created with their PC (create_student takes the PC's id now, not a name); a
student already on the roster with no PC has theirs chosen at this booking (assign_student_pc);
one who has a PC shows it, fixed. A teacher cannot change a PC once chosen — moving a student moves
every report they have to somebody else — so that is an admin's, from the booking form or the
student's row under Users. The rule is also the database's (0056): a session inserted by a
signed-in client for a student with no PC is refused (sessions_need_a_pc). Students from before
this keep the name that was typed until their next booking, where the form shows it as a reminder
and asks for the real one. profiles.pc stays, as the PC's name — what every screen and search
already prints — and a trigger keeps it in step with profiles.pc_id, renames included; the
free-text create_student(p_first, p_last, p_pc) and set_student_pc are gone (0056).
What a PC reads. Their students' sessions from the staff side, and all of each: every question
put up and the answer to it, the times and the diagnoses, the teacher's form, the transcript, the
reading of the recording, the report, and whether its email reached them. A session opens on the
admin's session page, from their seat — the same traces, and none of the controls. They read no
answer-key table (the report takes the key from the item once the results are published, when the
student has it too), no staged question, no bank and no other PC's students; they write nothing and
run nothing, because every write in the schema asks for a teacher or an admin and nothing adds one.
A signed-out PC following the link in an email signs in and lands on that session.
The email. Generating stamps session_reports.generated_at, and a trigger on that stamp queues
the notify_pc_report edge function the way 0058 queues the new-teacher notice: pg_net, no
secret, any failure swallowed — a report never fails to generate for want of an email. The function
reads everything again on the service role (the report really was generated, the student's PC, and
the address they sign in with), builds the PDF — the report page's own computation (buildReport,
buildGrid, buildReportDoc), laid out by lib/reportPdf.ts and drawn with pdf-lib — and sends it
with a link to the session. Every generation gets one row in report_emails (sent, failed with
the reason, or skipped with why), which is what the console, the admin's page and the PC show, and
what makes it send once: a replayed call finds the generation already claimed. Send it again on
the console is the session's teacher's and an admin's, as themselves. Generating again sends the new
report, marked as replacing the one before.
Targeted tests and progress
The question a PC asks, and a parent through them, is whether the teaching is working on the
skill the diagnostic found weak. The teacher decides everything here — when to test, what, how
long — and the platform computes what the answers say. Nothing books a test on its own and no
more mocks are pushed. The decisions behind it are in
docs/reference/targeted-tests-handoff.md.
A session says what it is for (0059): diagnostic or targeted. Every student's earliest
completed session in a subject was marked their diagnostic when it ran. The two are kept apart
for staff: Sessions has a Diagnostics tab and a Targeted tests tab, each with its own
columns (the levels sat; the skill and the set size) and its own New button, and each New button
opens its own form — /sessions/new for a diagnostic (time, length, meeting link) and
/sessions/new/targeted for a targeted test (a roster student, the skill and the set). A student
sees both in one list. The diagnostic form says when the student already has one — read across
every teacher — since only the first is the baseline; a later one counts as a session between
targeted tests. live is what sessions were before 0059 — a "lesson", the diagnostic's flow
under another name, counted the same way between tests. Nothing books one now, and every screen
files those rows with the diagnostics.
Booking a targeted test (0061). The teacher picks a section and a skill (or the whole
section), then how the questions are chosen, then the clocks. Choose them myself opens a
pop-up (components/TargetedPicker.tsx) on every question on the skill from
targeted_candidates: the levels as tabs, a box to tick on each question, "seen before" on any
the student has met, and the question clicked shown whole on the right the way the student will
see it — a skill's stems are often word for word the same. "Use these" brings the ticks back;
the set is sat easy → medium → hard. Pick for me draws a balanced set at random —
lib/targetedSet.ts: a size (5 by default, an admin's setting), easy → medium → hard (5 →
2/1/2, 10 → 3/3/4), unseen before seen, a seeded draw that Pick again replaces. A level the bank
cannot fill borrows from its neighbour and the form says so — "a balanced set needs 1 medium; 0
unseen — 1 short". Either way the form then shows the set as a numbered list with its mix by
level, and Edit reopens the pop-up on it. create_targeted_session books the session and its
set in one transaction and checks every question again; a ticked set allows repeats exactly
when a ticked question was seen. The set is the teacher's and an admin's to read: it is what the
student has not seen.
Explanations are drawn in the shape they were written in (lib/explanation.ts,
components/Explanation.tsx). The OnePrep items (0065) say why the key is right and why each
other choice is wrong, each with a named step and often a key phrase; the test page and the
student's results lay that out as OnePrep does — the key's reason, then one row per other
choice — and show OnePrep's accuracy and timing as figures under "Why easy". An explanation in
any other shape is shown as written, its line breaks kept.
Sitting it (0062). The student opens their link and presses Start — from the time it was
booked for, or before it if their teacher or an admin presses Let them start now on the
console (0025's waiver: the scheduled time stays what was arranged, and Undo early access puts
it back until they start; their waiting screen sees it within seconds). The server puts question
1 up, stamps when it did, and fixes its clock (the test's seconds a question, or the question's
own target). They answer or the clock runs out; either way the next question comes up. There is
no skipping and no going back, the screen says "Question 2 of 5", and both clocks count down in
the server's time. There is no cron: every call that touches the test expires whatever is overdue
first. A question whose time ran out is wrong, with no option. When the overall clock runs out
the question on screen is timed out too; End test on the console, or the student leaving, sets
it aside instead. A question never answered is not reached, and not reached is left out of
the score. What the link sends is a fixed list of fields: no level, difficulty, source, key or
result. Publish results gives the student their questions, answers and explanations, as after a
lesson; the scores and verdicts are for staff and the PC.
Reading it. The console and the report (/sessions/:id/report, its own page for a targeted
test) show, computed on read (lib/targetedAnalytics.ts): the score over what was reached, raw
out of 10 and mapped to a level score, always side by side; accuracy by difficulty; time
against target, with rushing, gaps and hesitation counted; what timed out; long questions against
short; the formats; and the same in sentences with their counts. Whether long questions are the
trouble is pooled over every test on the skill and only said with enough of each. The report opens
on the result in one sentence — right of reached, the score, the level score, and the change
since the point before with its verdict — and every figure says what it means under it, with
How is this scored? a click away: the score, the admin's mapping and thresholds, and what one
question is worth, in the settings' own numbers. Accuracy by level is drawn as bars.
A summary by Gemini. On the report and the student's progress page, staff can press
Generate summary for a few lines on every targeted test the student has sat in the subject:
a headline, at most four points, one next step. summarise_targeted_tests checks the caller is
a teacher or an admin, loads the numbers itself through student_progress as the caller, and
shows the model those numbers only — each skill's line, each test's score, change and reading
(lib/targetedSummary.ts) — never the questions or the answers; the prompt holds it to the
facts and to calling one question a small sample. Nothing is stored: pressing it again reads
again. It uses GEMINI_API_KEY, and SUMMARY_MODEL if set.
Progress (lib/progress.ts, 0063). The line on a skill starts at the earliest diagnostic
with answers on it, or at the first test when there is none, and runs through every test since,
counting the lessons and diagnostics between — whoever taught them. Each step is Improved,
Minimal change or Declined against the admin's thresholds, with the agreed wording and a caveat
from the mapping ("On 5 questions, one question is worth about 5 points"). The teacher can add
their own wording for a step and a summary for the PC; both are drafts until shared, and an edit
after sharing changes the draft only. A private note on the skill is staff-only.
/students/:id/progress shows every skill to staff and, read-only, to the student's PC.
student_progress(student, subject) is the one door: staff, the student's own PC and the
service role, numbers and ids only, and the shared copies — never a draft.
The PC sees a targeted test as soon as it is completed, with its computed verdict; the
teacher's wording and summary once shared. A targeted report is never emailed on its own:
Send to PC on the report is the teacher's or an admin's, and notify_pc_report keys each
send by when it went. The lesson report and its PDF carry a Progress section too.
Settings. An admin's page (/admin/settings), one tab per subject, reads as four questions:
how a score out of 10 becomes a level score (a table of points, default (0,20) (5,30) (8,40), the
last slope carrying on above it, with what every score from 0 to 10 comes to underneath); when a
change counts as Improved or Declined (±5, with what one question is worth on a test of the
default size); how many questions a new test starts at; and what makes a question long (English
75 words, Maths 40) and how sure the long-question flag has to be (4 of each, short right 40
percentage points more often). Nothing stored depends on them: a change re-reads every test.
The practice pool (0060) is a set per subject, listed in the bank with the three tests. A
question written into it says its own difficulty and must have a skill; no lesson control can
reach it, since they all look a test up by its level. English's pool holds 48 Cross-Text
Connections questions (0065, ENG-PRACTICE-CTC-Q01 … Q53): 14 easy, 17 medium, 17 hard,
transcribed from OnePrep with its accuracy and timing figures as each one's difficulty rationale.
The one rule that shapes the schema
Postgres RLS is row-level: a policy cannot hide a single column of a row it grants. And
Supabase Realtime pushes whole rows to subscribed clients. So anything a student must not
see lives in a table a student cannot read — which is why question_keys is its own table
rather than a correct_option column on questions. Once students start receiving questions
in live sessions, there is no row and no column for the answer to leak from.
The same rule covers the live session: is_correct lands the moment a student submits, long
before the teacher reveals, so it lives in session_item_assessments rather than on the item
row the student subscribes to. Result, correct option and explanation are copied onto the item
only when the teacher reveals — which is the only route by which any of them reach the student.
psql "$DATABASE_URL" -f supabase/tests/rls_contract.sql
psql "$DATABASE_URL" -f supabase/tests/session_flow.sql
psql "$DATABASE_URL" -f supabase/tests/level_session.sql
psql "$DATABASE_URL" -f supabase/tests/choosing.sql
psql "$DATABASE_URL" -f supabase/tests/opening_early.sql
psql "$DATABASE_URL" -f supabase/tests/authoring.sql
psql "$DATABASE_URL" -f supabase/tests/session_link.sql
psql "$DATABASE_URL" -f supabase/tests/admin_access.sql
psql "$DATABASE_URL" -f supabase/tests/pc_access.sql
psql "$DATABASE_URL" -f supabase/tests/modes.sql
psql "$DATABASE_URL" -f supabase/tests/practice_pool.sql
psql "$DATABASE_URL" -f supabase/tests/targeted.sql
psql "$DATABASE_URL" -f supabase/tests/targeted_link.sql
psql "$DATABASE_URL" -f supabase/tests/student_progress.sql
psql "$DATABASE_URL" -f supabase/tests/link_payload.sql
On a plain local Postgres, tools/local-db/rebuild.sh builds a scratch database from every
migration and runs every contract.
The revoke that does not revoke, three times. revoke execute … from anon is decorative:
Postgres grants EXECUTE to PUBLIC and anon is a member of PUBLIC, so revoking from the role
leaves the PUBLIC grant standing. 0018 found that and fixed three functions by revoking from
PUBLIC; 0028 found it again on load_session_level. 0035 found that revoking from PUBLIC is
only half of it — this project has
alter default privileges in schema public grant execute on functions to anon, authenticated, …;
so every new function in public is born with an explicit grant to anon that has nothing
to do with PUBLIC and survives revoking from it. A function can therefore have no PUBLIC grant,
an explicit grant to authenticated, and still be callable by anyone holding the publishable
key. That left four SECURITY DEFINER functions that check nothing — by design, because their
callers do — reachable anonymously: open_session_now, record_answer, end_session_now and
publish_one_item (open since 0023). 0035 revokes from anon and authenticated by name
as well as from PUBLIC, and session_link.sql now asserts the whole grant table, so the next
function written in this schema fails a test rather than repeating the note.
Watch for one trap when revoking: a column default is evaluated as the INSERTing role, so
sessions.access_token default new_session_token() broke every session insert the moment that
function was revoked from authenticated. The default carries the expression inline instead.
The report and session RPCs 0018 deferred are still granted to PUBLIC. Every one of them opens
with assert_session_teacher or a check on auth.uid(), so an anonymous caller gets an
exception rather than a session; tightening them touches is_teacher(), which RLS policies call
as the querying role, so it still wants its own test pass.
Between them these assert: a signup asking for admin is coerced to student; a teacher account
arrives active and is staff at once, and cannot switch itself off or rename itself;
an admin reads every profile and session and can edit and run a session they do not teach — which
admin_access.sql covers in full — but cannot switch themselves off; a student reads no profile
but their own and their teacher's; a suspended teacher loses the answer keys the moment they are suspended and cannot
sign in again at all; a student
cannot self-promote or author questions; a queued question is invisible and unanswerable; a
published question exposes the question and its options but never the key; after submitting,
the student cannot learn whether they were right; and the teacher's diagnosis is never visible
to the student. level_session.sql is the whole of the session flow: a student cannot open a
session early or open somebody else's, opening puts nothing up until the teacher shows a
question, exactly one question is within their reach at a time, answering puts nothing up, moving
level voids the question on screen and puts up the new test's first, what was already answered
survives the move, no question is asked twice even coming back down, only the teacher can move it
— not the student, not a stranger — and set_session_paper, publish_item and
set_level_by_token are gone. choosing.sql is the teacher choosing: only the session's teacher,
only while the test runs; starting it and answering put nothing up (0057); now sets aside the
question on screen, leaves exactly one open and queues nothing behind it; not now is refused
while a question is open and goes up at once when nothing is; and nothing answered, on screen,
set aside, in another subject's tests
or in no test at all can be chosen. admin_access.sql is the admin on a session they do not
teach: they let the student in early, start it, move the level, choose the question, answer for
the student, publish the results, diagnose, end it and edit it; they put in, correct and delete a
transcript, which is signed and re-dated when its text changes (and not when the same text is
saved again); they save the grid and the comments and hand the form in, under the form's own
checks, and form_submitted_by says so; they generate a report once the form is in, and not
before, and generated_by says who pressed it; a reading is dated when it is stored whatever it
is given, and refused once the transcript changed after it or the form was handed in again after
it — for the teacher too; generating without the recording removes a stale reading and leaves a
current one; they publish and unpublish, through the RPCs and by writing the report row, and the
teacher still can; and another teacher, a student and an anonymous caller can do none of it.
pc_access.sql is the PC (, ), 83 rows: is the service role's alone, wants
both names and an unused address, and the sign-in made after it leaves the profile a PC and spends
no student's serial; a signup asking to be a PC is a student; a teacher cannot book a student with no
PC, chooses one for a student who has none, and cannot change one already chosen, while an admin
can; nobody chooses a teacher or a suspended PC; the name follows the PC, renames included, and a
student cannot choose their own; staff read the PCs and a student does not; a PC reads their
student, the teachers who taught them, the session, the asked questions and their options, the
assessments, the form, the transcript, the reading and the report — and not a staged question, the
answer key, another PC's student or a question nobody put to theirs; a PC changes nothing and runs
nothing, and a suspended one reads nothing; generating queues the email and saving the report does
not; the log is read by the teacher, the PC and an admin and written by no client; a generation is
claimed once, again on request, and a failed one can be retried; a link goes only to an active PC,
works for a week, dies when another is sent, opens without being spent, is spent once, comes back if
the password was refused, and opens nothing once expired or once its PC is suspended; joining
outlasts later links; admins read the links and nobody else does, and no client writes one. Every
contract that books a
session gives its student a PC first, the way the booking form does.
covers the waiver: the scheduled time is a real gate, only the session's own teacher can lift it,
lifting it rewrites neither nor the status, and it cannot be taken back once the
student is in. Every row must read PASS.
rls_contract.sql and session_flow.sql are written for a scratch database — they reset the
display-id counters on their way out, and rls_contract.sql counts the whole bank, so its two
count rows read FAIL against a database the content migrations have been run on.
level_session.sql, choosing.sql, opening_early.sql, authoring.sql, session_link.sql and
admin_access.sql leave the counters alone and are safe against a real one; level_session.sql, choosing.sql,
session_link.sql and admin_access.sql's console rows need the three tests loaded (0026), and authoring.sql's filing section needs
the mathematics easy test (0040) — without it those rows read SKIP rather than FAIL. A teacher
account arrives active since 0058, so a contract that acts as a teacher uses the one its signup
made. session_link.sql is the contract for the three doors 0032–0034 opened: a teacher can
add a roster student and a student cannot; a session's token is unique, opens only its own
session, and never returns the key or the token itself; a link cannot answer another session's
question; and only a session's own teacher can open it, answer in it or end it.
Layout
apps/web/ React + TypeScript + Vite → Vercel
supabase/migrations/ schema, RLS, and the session RPCs
supabase/tests/ the security contracts
docs/ system design for everything not yet built
tools/ PDF question extractor (for the screenshot library, later)
apps/api (Fastify on Render) is still not needed. Grading has to run somewhere the student
cannot reach question_keys, and submit_answer does that as a SECURITY DEFINER function
inside Postgres — which is server-side already. Render earns its place when the transcript and
report pipeline arrives; see docs/03-architecture.md.
Deploying
Vercel — set the project root to apps/web; vercel.json already handles the SPA rewrite.
Set VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY in the project's environment variables.
Supabase — migrations in supabase/migrations are ordered and idempotent to apply in
sequence. Do not change the schema from the dashboard; RLS policies are exactly the thing you
cannot afford to have drift undocumented.
The edge functions — reading the recording runs server-side, because the key would otherwise
ship to every browser and the quote check would be a promise the client makes about itself:
supabase secrets set GEMINI_API_KEY=...
supabase functions deploy extract_session_context
supabase functions deploy summarise_targeted_tests # the targeted tests' summary button, same key
Deploy it again after any change to it or to the library files it imports. The deployed copy is
not checked against the repository by anything, and it drifts: 0048 put the admin's check into
it and left the live copy at 4c007e9's, so for as long as it stayed there an admin's Generate got
"not your session" from the reading — which is how the first admin to use it met the wall. It was
redeployed with 0050. node tools/bundle-edge-function.mjs flattens every function and the library
and _shared/ files it imports into dist/edge/<function>/ for a deploy through the Management
API, which resolves files flat (node tools/bundle-edge-function.mjs manage_pc does one). The files
are found by following the imports, not listed by hand.
Mail. Three functions send mail — the new-teacher notice, a PC's link and a report to a PC —
and they all send it one way (_shared/send.ts). Supabase's own mailer cannot do it: it sends its
auth templates and nothing else, carries no attachment, and without a custom SMTP server refuses
every address outside the project's team. So the functions speak SMTP themselves, with an app
password on the sending account, on port 465 — TLS from the first byte, because hosted
functions may not open 25 or 587 at all. For a Google Workspace or Gmail account (2-step
verification on, then Security → App passwords):
supabase secrets set SMTP_HOST=smtp.gmail.com
supabase secrets set [email protected] # the account the app password is for
supabase secrets set SMTP_PASS='abcd efgh ijkl mnop' # the app password; the spaces are dropped
supabase secrets set MAIL_FROM='Ascend Now <[email protected]>' # optional: that account or a verified alias
supabase secrets set APP_URL=https://sat-teachers.vercel.app # optional; this is the default, for links
SMTP_PORT defaults to 465. RESEND_API_KEY still works instead of the three SMTP secrets. With
neither, nothing is sent and nothing breaks: new teachers are still on the admin's dashboard, a PC's
link is shown to the admin once, and a report's email is recorded as not sent with the reason,
with Send it to the PC on the console for once it is set up.
supabase functions deploy notify_new_teacher --no-verify-jwt # the database calls it, with no token
supabase functions deploy notify_pc_report --no-verify-jwt # the database calls it, with no token
supabase functions deploy manage_pc # an admin's browser calls it, signed in
supabase functions deploy accept_pc_invite --no-verify-jwt # the join page calls it, signed out
# once per project, so the triggers know where to call:
# insert into app_config (key, value)
# values ('functions_url', 'https://<ref>.supabase.co/functions/v1')
# on conflict (key) do update set value = excluded.value;
Order, for 0054–0056. Two steps, so the app being served never meets a database it cannot
use. 0054 and 0055 add everything and take nothing away: the app from before them keeps adding
students with a typed PC and booking students who have none, and the new app works too. So apply
those two and deploy the functions first; then merge, and Vercel deploys the app that chooses a PC;
then add the PCs under Users → PCs; then apply 0056, which drops the free-text
create_student and set_student_pc and makes the database refuse a session for a student with no
PC. Applied before the new app is live, 0056 would stop every booking until it was. After it,
nobody can be booked until a PC exists — that is the rule, not a bug.
Order, for 0059–0064. All six are safe for the app being served before them: the new
column has a default, create_question_in_set's new argument is optional, every existing screen
filters the bank on level is not null, and each changed token function keeps a lesson on the
code path it had. Apply them in order, then deploy notify_pc_report (its targeted branch and
the Progress section), then merge. The Supabase MCP tool's apply_migration waits for a
confirmation on any SQL holding drop or delete and times out when nobody gives it, so those
migrations go through the SQL editor — see the handoff note.
Gemini reads the recording, and apps/web/src/lib/gemini.ts is the only file that knows it —
the guard, the prompt, the schema and the report are written against a shape, not a vendor. The
model id defaults to gemini-2.5-pro and EXTRACTION_MODEL overrides it, so a rename is a secret
change rather than a deploy.
To check a prompt change or a new model id against a real recording before deploying it,
tools/bench-extraction.mjs runs one transcript through with no database in the way and reports the
drop rate — how often the model was asked for a verbatim quote and did not give one:
GEMINI_API_KEY=… node tools/bench-extraction.mjs transcript.txt 23
It imports the guard from apps/web/src/lib/ rather than carrying its own copy, so the modules
the vitest suite protects are the ones that actually run. Without the secret the function returns
a 500 saying so, and the rest of the report still works — a reading is an enrichment, not a
dependency.
Email confirmation is on by default. To let people in immediately after signing up, turn it
off under Authentication → Sign In / Providers → Email. The signup screen handles both.
Where the confirmation link goes. Under Authentication → URL Configuration:
| Setting | Value |
|---|
| Site URL | https://sat-teachers.vercel.app |
| Redirect URLs | https://sat-teachers.vercel.app/**, and http://localhost:5173/** for local work |
Signup asks for the link to come back to the app it was made from (emailRedirectTo), and Supabase
only does that for an address on the Redirect URLs list — anything else goes to the Site URL. That
was localhost on this project, so new teachers confirmed their email and then landed on "this site
can't be reached". With both set, the link signs them in and they are in the app. A Vercel preview deployment needs its own pattern on the
list (Supabase's docs give one for Vercel) or its links fall back to the Site URL too.
