Which children get tested, and how results are stored: review of the staging EGRA/EGMA build

Reviewed 27 Sep 2026 (bead bd-ak1go). Read-only: I edited no code and checked out no branch. The code is the staging snapshot in review-staging-build/src/, so every src/...:line below points into that folder. The database queries ran on the shared Rumi Supabase as analyst2 and return counts only. No child's name appears in this note. Where I cite the logs, IDs are hashed.

---

The short version

1. The children are not chosen the way the call decided. On 15 Sep the team agreed on a pre-drawn list that the coach does not influence, with named alternates for absent children. The build does something different. The coach picks a class from a list, and the bot then draws one child from that class. Each of the five children is a separate draw, and the same child can come up again. Nothing stops a coach from cancelling and drawing again, and there are no alternates.
2. Nobody can check afterwards which child was drawn. The draw is not saved in the database. The results table has a student_id column, but the code never fills it. The only record of a draw is a log line, and that line does not carry the session id.
3. Redrawing takes about 15 seconds. Staging logs from 18 Sep show a tester doing it twice in five minutes: /cancel, then /egra, then the same class again. Neither attempt left anything in the database.
4. The pool it draws from is tiny. The build draws from 7 classes and 41 children. The largest class has 12 children, so no class can supply the 15 per class the call named as the benchmark. There is no grade 3 class at all. The call cited about 1,700 Islamabad grade-sections, and those are not in this table.
5. The results table cannot support the plan's AI-versus-coach calibration. A database rule permanently blocks it from holding an AI score. It has no per-item AI marks, and its audio columns were never added to the live database. Its row-level security is also switched off (see §3.6).
6. The fix mostly reuses code we already have. shared/services/reading/student-selection.service.js is already on staging. It picks several children at once, guarantees the whole class is reached before anyone repeats, checks today's attendance, and takes an injectable random-number generator. The EGRA build did not use it. The missing pieces are a saved, seeded draw with ordered alternates, reason codes for absent children, and a rule for new versus repeat children.

---

1. How the build picks a child today

What the build does, step by step. The coach types /egra. The bot sends a WhatsApp list of classes, one row per teacher's class, across all ICT-region teachers. The coach taps a class. The bot picks one active child from that class at random and names them ("Selected Name (Grade 5 A) — starting the assessment"). The full test then runs for that one child. For the next child, the coach starts again from /egra.

What the call decided (15 Sep 2026)

• The children are pre-selected and the coach does not choose. Haroon at 3127s: "Coach نہیں select کرے گا کس بچے کا test کرنا ہے۔ یہ سارے پہلے سے pre-select ہوئے ہوں گے" ("the coach won't select which child to test; they will all be pre-selected"). The coach receives names and a class and pulls those children out.
• The app says which child, and replacements are built in for absent children. Haroon at 818s: "app بتائے گی کہ اس بچے کو کرو… with replacements factored in if the kid is absent" ("the app will say: test this child…").
• The impact team's form looks only at the register and names roll numbers plus alternates (3562s: "pick number 3، pick number 6، pick number 7، اور ان کے وہ alternates"). The ASER study used odd roll numbers 1, 3, 5 … 29, which gives 15 per class. An absent child was replaced by the next even number (3626s).
• Five children per visit (3685–3698s). The mix was still being debated: five new children, or three new plus two repeats (818s and 1247s). By the end the chair leaned towards "EGRA plus 3 to 5 testing, new students, up until 3 months" (4470s), with repeats after three months (4374–4398s). PLAN.html D5 still lists this as open.
• The benchmark for detecting 0.2 SD was about 15 children per class or grade (1721s and 3723s). Change within a month would be nearer 0.05 SD, which needs "the entire class" (3788s) and "population size and EGRA" (4465s).

Point by point

#	Question	What the build does (evidence)	What the call decided	Why it matters
1a	What is randomised?	Only the child within a class the coach has already picked. Code: getRandomActiveStudent(listId), src/shared/services/egra-egma-ict-test/roster.service.js:106.	The children and the class, drawn from the register.	The coach controls half of the draw.
1b	Who picks the class?	The coach, from a list showing each class's teacher name, school, grade, section and size. The build's own testing notes admit it: "The coach still picks WHICH CLASS to test… a coach could always pick their strongest class" (src/TESTING.md:233-238).	Not the coach (3127s, 3597s).	This is the largest source of bias (see "Why this biases the numbers" below).
1c	Which classes are on the list?	Every class of every ICT-region teacher, not only the school being visited. The query filters only on region in (ict, islamabad, federal) (roster.service.js:53-62). The list is also cut at 9 rows (handler.js:37 MAX_CLASS_ROWS = 9, :121 classes.slice(0, MAX_CLASS_ROWS)) with no sort order.	The school being visited, from the algorithm.	At Islamabad scale only 9 classes could ever appear, in whatever order the database happens to return them. Nothing links a coach to the school they are in.
1d	One child per tap, or a list of 5?	One child per tap. Each tap is a fresh draw from the whole class, so the same child can be drawn again (roster.service.js:123 Math.floor(Math.random() * data.length)). Nothing excludes children already drawn.	One pre-drawn list of 5 for the visit.	If five children come from one class, the chance of a repeat is 62% for a class of 12, 74% for 9, and 85% for 7. When that happens the coach has to decide what to do, which puts the choice back in their hands.
1e	Fixed starting point for the random pick?	None. It uses Math.random(), which cannot be replayed.	Not discussed. The register-based form is deterministic, so it can be replayed.	The draw itself is fair. The problem is that nobody can re-run it later to confirm which child it picked.
1f	Is the draw saved?	No, only in the logs. The chosen child's id goes into the session memory, labelled "additive — kept for potential future use" (handler.js:669-670), which expires after 1 hour (session-state.service.js:21). It is never passed to the database write (handler.js:375-392 and :434-449 pass studentIdentifier only). results.service.js:33-60 has no student_id in what it saves. Axiom gets student_selected_from_roster {coachUserId, listId, studentId} (handler.js:682-684), with no session id, class size, rank or seed.	The algorithm names the children, so the list exists before the visit.	You cannot compare who was drawn with who was tested. The one live row shows this: its name matches a roster child exactly, but student_id is empty (query Q3).
1g	Can a coach redraw?	Yes. /cancel is accepted at any step after the class list (handler.js:539-543). Then /egra and a tap on the same class gives a fresh draw. Opening the old list again does nothing (handler.js:636). Staging logs on 18 Sep: a /cancel at 12:32:26 was followed by a new session and draw at 12:32:46, and a /cancel at 12:36:36 by a new draw at 12:36:56. Both attempts left no database row (query A3). These were internal testers, so the logs prove the mechanism works, not that anyone meant to game it.	Not possible, because the list is fixed in advance.	A coach who doesn't like the drawn child can get another in about 15 seconds without leaving any trace in the results.
1h	Can the draw be skipped entirely?	Yes. The last row of the list is "Type a name instead" (handler.js:122-126). A typed name is saved exactly like a drawn one. Both have an empty student_id and nothing records how the child was chosen.	Not provided for.	Hand-picked and randomly drawn children look the same in the data.
1i	Absent or refusing children	Not handled. There are no alternates and no reason codes. The coach redraws or types a name.	Named alternates, next in order (3562s, 3626s).	Absent children tend to be weaker learners. If the coach quietly replaces them, the sample drifts upwards and we can't measure by how much.
1j	Repeat testing across visits	The draw ignores history. Because the child's id is never saved, the system can't know who was tested before.	First term all new, repeats after 3 months (4398s, 4470s). The 3+2 option is still open.	It can do neither "5 new" nor "3 new + 2 repeat". Repeats happen by chance. Staging shows one 2-child class drawn five times, with the same child drawn in four of them.
1k	Children who have left	Excluded, because it filters on students.is_active = true (roster.service.js:74, :113). But it does not filter on student_lists.is_active or academic_year. Live, 1 of the 8 ICT lists is from academic year 2025-2026 and is offered (query Q5).	Implied by "register".	Last year's classes can be offered.
1l	Small classes	Offered like any other. Live active sizes in the frame are 0, 1, 1, 2, 7, 9, 9 and 12. Classes of 1 or 2 give no real randomness.	15 per class for 0.2 SD (1721s, 3723s). Small-school counts were raised at 3708s.	None of the offered classes can reach 15. There is also no weighting, so a child from a class of 1 counts the same as a child from a class of 40.
1m	Split by grade	None. The grade is shown in the list, but it is not used in the draw or saved on the result row. The live frame has grades 1, 2, 4 and 5, and no grade 3.	The grade question is open (grade 3 only, or 2 to 4; 1967s). Draws should be within grade.	Results can't be broken down by grade without re-deriving it.
1n	Same-name children	The roll number is fetched (roster.service.js:111) but not shown. The confirmation gives only name and class (handler.js:678-681). Across the whole database, 25 of 375 classes have at least two active children with the same name. There are none in the ICT test frame today.	The form names roll numbers (3562s).	The coach could pull out the wrong child.

A small point on tests: roster.service.test.js:156 is titled "picks uniformly … (statistical check …)". It actually checks only that a random value of 0 picks the first child and 0.99 picks the last (:172-175). It shows the index mapping works, not that the draw is uniform.

Why this biases the numbers

Picture a coach visiting a school where Grade 5 A is strong and Grade 5 B is struggling. The build lets them tap 5 A. Then, if the drawn child is one they know is weak, or is absent, they can type /cancel and draw again. Neither step leaves a mark in the results. Coaches don't need to intend any of this. It is simply easier to test the class you have just observed, or the children who are in the room. Over hundreds of visits, those small nudges add up to a sample that is better than the real classroom. The average looks higher than it is, and it can look as if it is rising when really coaches are getting more comfortable with the process.

The call's design removes every one of those choices. The class and the children are set before the coach arrives, absent children are replaced by the next name on a fixed list, and every change is recorded. That is also why the impact team's form "is not looking at any other data while picking besides the roster" (3562s).

---

2. The right way to do it

The goal is a draw that happens once, on our server, before the coach sees any name. It should be recorded, replayable by us, give the coach no choice, and still work over WhatsApp.

2.1 The rules

1. Build the list of eligible children once per cycle. A cycle is one measurement window. The call's recommendation is EGRA every three months, so a quarter such as ICT-2026-Q4. The eligible children are every active child in active, current-year classes in the target grades at each school. Take a snapshot of that list when the cycle starts.
2. Shuffle each class once per cycle, on the server. - Build the starting number from a secret key plus the class id plus the cycle id. The secret stops anyone predicting the order. Storing it lets us re-run the shuffle for an audit. - Shuffle the class's children with that number. Each child gets a rank from 1 to N. - Save every rank before any name is shown. - Why per cycle rather than per visit date: a seed based on the visit date is also replayable, but each visit draws independently. Children then repeat within the quarter by chance, nothing guarantees the whole class is reached, and a coach can change which list they get by changing the day they visit. One order per class per cycle avoids all three problems.
3. At each visit, take the next children in rank order. The server takes the next 5 pending children at the visited school, spread across its target classes in turn. The coach receives one message listing the 5 names with roll numbers, plus the next 2 or 3 in order marked as alternates.
4. Absent children are coded, not replaced by choice. The coach taps a reason: absent, child refused, teacher declined, left school, not on register, cannot be assessed, or ran out of time. The next alternate then moves up automatically. An absent child is not dropped. They go back into the queue at their original rank and are tried again at the next visit (say up to two tries). Only then are they coded absent_final. Absent children are usually weaker learners, so replacing them permanently biases the results upwards. The ASER study replaced them with the next even roll number, which is fine for a single survey. A rolling programme can do better by going back for them.
5. No redraws. /cancel does not release anyone. Reopening shows the same list with each child's status. The only way past a child is to give a reason, and the reason is saved with a timestamp. Supervisors can then compare each coach's absence rate with that school's attendance. That comparison is weak for now: only 10 attendance sessions in the last 30 days, query Q8.
6. Manual entry is not in the coach's path. Either remove "Type a name instead", or keep it for supervisors with a required reason, save it as selection_method = 'manual', and leave it out of outcome estimates.
7. Small classes are tested in full. If a class has no more children than the quota (15), everyone is tested, with weight 1.
8. Record each child's chance of being picked. Save the class size at the time of the draw on every row, so region figures can be weighted by class size / number tested in the class. Classes range from 1 to 40, so leaving this out quietly over-weights small classes.

2.2 "5 new" or "3 new + 2 repeat": one setting either way

Make both a region setting, new_per_visit and panel_per_visit, so the draw code doesn't change when D5 is decided.

• Cycle 1 (first three months): 5 new, 0 repeat. This matches where the call ended up (4398s, 4470s) and quickly establishes a baseline for the whole class.
• Cycle 2 onwards: the repeat children are cycle 1's tested children, taken in their cycle-1 rank order, each retested at least about three months after their first test. Because they come from the top of a random order, the repeat group is random too, not "the children the coach remembers". This also answers the concern at 1813s that retesting within weeks mostly adds practice effects and reduces the effective sample.

2.3 How many children? The arithmetic behind the call's numbers

Two facts from the call fix the shape of the design. A coach visits each school about 6 times a quarter (4378s), and does 5 children per visit. That is 30 tests per school per quarter, which is 15 each in two target classes (one grade with two sections, or two grades). With three or more target classes per school, each class drops below 15.

What those numbers can detect is below. I assumed the usual 80% power at a 5% significance level. The class-level clustering (ICC 0.2 to 0.3) and the correlation between a child's two scores (0.7) are my assumptions, not measured. Treat the table as orders of magnitude.

What we want to detect	Design	Children needed per measurement wave
A 0.2 SD change in one class	15 new children each wave	Not detectable. The smallest detectable change is about 1.0 SD.
A 0.2 SD change in one class	Same 15 children retested	Still not detectable. The smallest detectable change is about 0.56 SD.
A 0.2 SD change across a region	New children, 15 per class	~1,500 children (~100 classes)
A 0.2 SD change across a region	Same children retested, 15 per class	~280 children (~19 classes)
A 0.1 SD change across a region	New children, 15 per class	~6,000 children
A 0.05 SD change (about one month's learning, 3788s)	New children, 15 per class	~24,000 children, which is effectively everyone. This is why the call said "population size".

In plain words, 15 per class is the right amount to test in each class. But 0.2 SD only becomes visible once results are added up across roughly 100 classes, or across about 20 classes if the same children are retested. Haroon's figure at 1866s of about 1,485 children a week, with 891 of them repeats, is in the right range for a regional 0.2 SD reading. Whether a month-on-month change can be read is set by the fourth row, and that only works with whole-class testing.

2.4 What needs storing

Where it could go. I checked the live schema first, as Rule 15 asks. There is no existing place for "the order of a class for this cycle".

• aser_sessions has the right basic columns: user_id, student_id linked to students, school_id linked to schools, grade_level, region and status. It is used for the actual tests.
• But origin/main:shared/services/aser/aser-roster.service.js:41-45 treats every aser_sessions row for a child as "already assessed", with no status filter. Adding "pending" draw rows there would make ASER's auto-pick skip drawn children that haven't been tested.
• The list of eligible children can't be rebuilt afterwards either. students keeps no history (only created_at/updated_at and an is_active switch), so a child who was active on draw day but inactive later disappears.

My recommendation: one small table, assessment_draws, with one row per drawn child per cycle. It is effectively the aser_assignments table the earlier coach-flow plan named (the migration comment mentions it at pending_egra_egma_ict_test_attempts.sql:16). The zero-new-table alternative is to put these rows in aser_sessions and add status <> 'pending' to that ASER consumer.

Field	Why
id, cycle_id, region	Which window this draw belongs to.
school_id → schools, list_id → student_lists, grade (snapshot)	Which school, class and grade. Note that 0 of the 6 ICT teachers with rosters have users.school_id set (query Q7), so the coach-to-school link has to be filled in first.
student_id → students, roll_number (snapshot)	Who. Store the id and roll number, never the name.
frame_size, draw_rank	The chance of being picked, and the order.
seed_digest, algo_version	What we need to re-run the shuffle.
sample_role (new or panel), attempts	Whether this is a new child or a repeat, and how many times they were sought.
outcome (checked list of values, default pending), outcome_at, outcome_note	tested / absent / absent_final / refused_child / teacher_declined / left_school / not_on_register / cannot_assess / time_ran_out / not_reached
assigned_coach_id, visit_date	Who was supposed to test the child, and when.
aser_session_id → aser_sessions	Links to the test that actually happened.
Unique keys: (cycle_id, list_id, draw_rank) and (cycle_id, student_id, sample_role)	Stop duplicate draws.

Each actual test row, whether in aser_sessions or the EGRA attempts table, also needs draw_id, selection_method (random_draw or manual), and a filled-in student_id.

How it feels on WhatsApp. Once the lesson recording arrives (the PLAN.html "offer" step), the coach gets one list message: "Today's children: 1. Roll 7 · Grade 3A · [name] … Alternates: Roll 12, Roll 4". A row per child opens buttons: Start test / Absent / Refused / Other. That fits inside WhatsApp's 10-row list limit.

Reuse. The ordering, attendance check and roll-number label already exist in shared/services/reading/student-selection.service.js, which is on staging and identical on main. It covers the never-tested-first ordering (:53-70), picking N children (:80-85), the attendance gate (:157-187) and the roll-number label (:228-239). For this purpose it needs three changes: a seeded generator instead of Math.random, the order saved rather than recalculated on every call, and alternates plus outcome codes.

---

3. Review of the data model (egra_egma_ict_test_attempts)

3.1 What exists live

• The base table exists and is in use, even though the migration's header still says "NOT YET APPLIED". It has 23 columns, 5 constraints and 3 valid indexes, and holds 1 row: one listening_comprehension_urd result from 18 Sep (queries Q1 and Q2).
• The voice-note migration has not been applied. audio_url, audio_whatsapp_media_id and audio_recorded_at do not exist (query Q1).
• This can already lose data. The listening-comprehension Flow is live on staging: the flow id is set and the Flow is published, per findings/live.md. Four staging sessions have reached the voice-note step. The first time a voice note is captured, results.service.js:57-59 will send audio_url for a column that doesn't exist. The database will reject that insert, so that subtest's row won't be saved.
• The coach won't know. The handler ignores the save result (handler.js:434 is not assigned) and says "Assessment complete" anyway. Grid subtests are the same: handler.js:394-397 sends "Recorded: …" whether or not the save worked.
• Staging so far, 18–24 Sep: 9 sessions started, 8 children drawn and 1 row saved (Axiom query A1). The database can't show that sessions were abandoned, because there is no sessions table and no completion marker.

3.2 What one row is, and its keys

• One row is one subtest attempt, for one child, in one session. A full test is 14 rows.
• Keys:
• The primary key is a uuid.
• session_id is free text generated in the app (session-state.service.js:70), with no table behind it.
• Nothing prevents the same (session_id, subtest_key) being saved twice.
• coach_user_id is linked to users.
• student_id is linked to students with ON DELETE SET NULL, but it is always empty. The column comment still says "always NULL in this build" (migration:44), even though the roster commit made the id available.
• There is no class, school or grade column.
• The instrument's own item names are kept in item_answers[].field_name, and item_bank_version is stored. Both are good.

3.3 Can it hold AI marks next to coach marks, per item? No.

• A database rule blocks AI scores entirely. rumi_auto_score_status is fixed by a CHECK constraint to 'not_attempted_this_build', and that constraint is live (query Q2). No AI verdict can be stored until someone changes the table.
• There are no AI or confidence columns at item or subtest level.
• Grid subtests store only the coach's marks. That means the list of wrong items and where the child stopped. The individual item marks can be worked out from those, which is good, but there is no word-level table or timestamps.
• The timing is approximate. elapsed_seconds is the time between the server receiving the Start tap and the Stop tap (handler.js:574, :582). It includes WhatsApp delivery delay, so it is not the child's actual reading time, and the rate per minute is calculated from it.
• Audio is limited. At most one recording per subtest, and only for listening comprehension. There is none for the timed reading (ORF) or letter tasks, which are exactly the ones the plan wants the AI to score. Those columns don't exist live, and no voice note has been captured (A1).

So it can't support the plan's calibration loop. That loop saves the computer's answer once, lets the coach's version record what changed item by item, and switches a subtask over only when agreement matches a second human. The existing reading_assessments table already has most of what that loop needs: student_id (linked), aser_session_id (linked), audio_url, word_timestamps, errors, words_correct, wcpm, and the paired rumi_level/coach_level/rumi_coach_delta/coach_user_id columns (query Q6). numeracy_assessments has the same paired columns plus a photo URL, although it has no student_id of its own.

3.4 Should an existing table have been used?

Partly, yes. PLAN.html (id="data") said "We add no new tables for version 1, only a few columns". It mapped the session to aser_sessions and each reading window to reading_assessments, with numeracy in numeracy_assessments.

• The migration's reason for a new table rests on a stale check. It says aser_sessions was "NOT YET SHIPPED … confirmed live 2026-09-16: no matching migration file exists" (migration:16-19). That checked for migration files, not the database. Live, aser_sessions exists with 84 rows, and the ASER flow on origin/main writes to it (Q4, plus the code search in the appendix).
• The build does have a fair point about shape. The grid "wrong items + where the child stopped" format and the EGMA item-by-item format don't fit one-row-per-subject ASER rows well.
• My verdict: keeping this table as a record of coach marks for the internal test is reasonable. It should not become the main EGRA store unless it gets: 1. student_id filled in, 2. a link to the draw, 3. a link to aser_sessions, 4. per-item AI verdict and confidence fields, with the CHECK pin lifted once validated scoring exists, 5. its audio columns.

Otherwise it should be merged into the PLAN's mapping.

3.5 Children's personal data

• The child's full name is stored as free text (student_identifier) on every one of the 14 rows per child, while the proper student_id column goes unused. Store the id and leave the name null for drawn children.
• Every /egra pulls every ICT child's name (roster.service.js:59, students ( id, student_name, roll_number, is_active )), even though the names are only used to count children. Select just id, is_active.
• Teacher phone numbers are copied into session memory (pendingClasses includes phoneNumber, roster.service.js:80, handler.js:489). They aren't needed there.
• The recording file name has no child name (egra-egma-ict-test/voicenotes/<session_id>/<subtest_key>.ogg, handler.js:778). That is good.

3.6 Access control: needs fixing before real children are tested

• Row-level security is off on egra_egma_ict_test_attempts (relrowsecurity = f, query Q9).
• The public anon and authenticated roles have full rights on it (arwdDxtm). That means anyone holding the project's public anon key could read children's names from this table, and insert or delete rows.
• By contrast, students and reading_assessments have row-level security on, so the same grants there are harmless.
• aser_sessions has the same exposure (row-level security off), but that predates this build.
• The fix is one statement per table: ALTER TABLE … ENABLE ROW LEVEL SECURITY;. The bot uses the service_role key, which bypasses row-level security, so the bot keeps working.

---

4. What was done well

• The draw is server-side and uniform within a class, and it uses the real roster. It keeps only active children, drops empty classes, and shows the live active count rather than the stale student_count (the test at handler.test.js asserts "2 students", not 9). That is a real improvement on a typed name.
• The draw logs the child's id, not the name. That log line is the only reason this review could reconstruct the staging redraws at all.
• It never dead-ends. A roster failure, an empty class or a failed voice upload all fall back to a working path (roster.service.js:29-33, handler.js:654-666, :780-784).
• The build is honest about its own weak point. TESTING.md flags the class-choice bias itself, and says plainly that it hasn't been checked against live WhatsApp.
• The migration is careful:
• it can be re-run safely and has a reversal step;
• it snapshots region at test time;
• it stores item_bank_version and the instrument's own item field names;
• it reserves student_id with ON DELETE SET NULL;
• the CHECK constraint that stops an unvalidated AI score posing as the real score is a principled safety choice;
• the indexes are valid (query Q2).
• The audio fields are sent only when present, so a partial migration doesn't break the other subtests. It just doesn't protect the listening row (§3.1).
• The grid scoring keeps enough to recover each item's mark (wrong-item list plus stopping point), which is what a later comparison with AI scoring needs.

---

Appendix: queries run (read-only, counts only)

All queries ran via .claude/skills/database-analysis/bin/rumidb as analyst2.

Q1: tables and columns

SELECT table_name FROM information_schema.tables WHERE table_schema='public'
  AND (table_name ILIKE '%student%' OR table_name ILIKE '%egra%' OR table_name ILIKE '%aser%' OR ...);
-- aser_results_flat(VIEW), aser_sessions, attendance_records, attendance_sessions,
-- egra_egma_ict_test_attempts, numeracy_assessments, reading_assessments, student_lists, students, ...
SELECT column_name FROM information_schema.columns WHERE table_name='egra_egma_ict_test_attempts';
-- 23 columns; no audio_url / audio_whatsapp_media_id / audio_recorded_at
-- students: no gender, no date of birth, no attendance, no grade column (grade is on student_lists)

Q2: attempts table contents and constraints

SELECT count(*), count(DISTINCT session_id), count(student_id), count(student_identifier) FROM egra_egma_ict_test_attempts;
-- 1 | 1 | 0 | 1   (subtest listening_comprehension_urd, region ict, 2026-09-18 06:30 UTC)
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='public.egra_egma_ict_test_attempts'::regclass;
-- CHECK (rumi_auto_score_status = 'not_attempted_this_build'); student_id FK students ON DELETE SET NULL; ...
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid='public.egra_egma_ict_test_attempts'::regclass;
-- pkey, _session, _coach: all valid

Q3: is the saved row a drawn child? (count only)

SELECT count(*) FILTER (WHERE EXISTS (SELECT 1 FROM students s WHERE s.student_name = a.student_identifier)),
       count(*) FILTER (WHERE a.student_id IS NULL) FROM egra_egma_ict_test_attempts a;
-- 1 | 1  -> the name matches a roster child; the student_id was dropped

Q4: existing ASER tables

SELECT count(*), count(student_id) FROM aser_sessions;          -- 84 | 0 (all status in_progress, 0 with school_id)
SELECT count(*), count(student_id) FROM reading_assessments;    -- 937 | 78
SELECT count(*) FROM numeracy_assessments;                      -- 113 (98 linked to aser_session_id)

Q5: the pool the roster service draws from (users.region in ict/islamabad/federal)

-- 6 teachers (5 federal, 1 ict), 8 lists, 41 students, all 41 active; 0 inactive lists;
-- academic_year: 2025-2026 = 1 list (1 child), 2026-2027 = 7 lists (40 children)
-- per-list active sizes: 0, 1, 1, 2, 7, 9, 9, 12; grades present: 1, 2, 4, 5 (no grade 3)
-- roll_number: 0 missing, 0 duplicates within a list; same-name children in ICT lists: 0

Q6: whole database, for context

-- student_lists 375: 158 empty, 134 with 1–4 active, 48 with 5–14, 35 with 15 or more; median active (non-empty) = 2
-- students 2,750: 2,592 active; 1 active child without a roll number; 0 lists with duplicate roll numbers
-- 25 lists have at least two active children with an identical name (23 of them with 2–3)

Q7: link from teacher to school

SELECT count(*), count(school_id) FROM (SELECT DISTINCT u.id, u.school_id FROM users u
  JOIN student_lists sl ON sl.user_id=u.id WHERE u.region IN ('ict','islamabad','federal')) u;   -- 6 | 0

Q8: attendance (possible check on absences)

-- attendance_sessions: 161 total, 10 in the last 30 days (9 lists), 11 ever in ICT scope

Q9: row-level security and grants

SELECT relname, relrowsecurity FROM pg_class WHERE relname IN (...);
-- egra_egma_ict_test_attempts f | aser_sessions f | students t | student_lists t | reading_assessments t
SELECT has_table_privilege('anon','public.egra_egma_ict_test_attempts','SELECT');   -- t (also INSERT t)
-- relacl: {anon=arwdDxtm/postgres, authenticated=arwdDxtm/postgres}

A1: Axiom digital-coach-logs, service digital-coach-staging, last 20 days

event startswith "egra_egma_ict_test" | summarize count() by event
-- session_started 9 · student_selected_from_roster 8 · listening_flow_completed 4 · subtest_recorded 1
-- no voice_note_captured, no persist_failed

A2: draws by class (ids hashed)

-- 2 tester accounts drew. The class with 2 active children was drawn 5 times (one child 4 times).
-- The class with 1 active child was drawn twice (same child).

A3: redraws

msg contains "/cancel"  (staging)
-- 2026-09-18 12:32:26 /cancel -> 12:32:31 session_started -> 12:32:46 draw
-- 2026-09-18 12:36:36 /cancel -> 12:36:46 session_started -> 12:36:56 draw
-- 2026-09-19 04:48:33 /cancel -> 04:48:48 session_started -> 04:48:53 draw
-- none of these draws led to a saved row

Code search

git grep -l aser_sessions origin/main -- '*.js'   -> aser-flow.handler.js, aser-assessment.service.js, aser-roster.service.js (+ tests)
git grep -l aser_sessions origin/staging -- '*.js' -> none (the ASER flow is on main, not staging)
student-selection.service.js: identical on origin/main and origin/staging

Transcript (meetings/probe2_soniox_alignment_call.mp3.md): lines 55–72 (1247–1888s), 128–140 (3052–3181s), 150–166 (3523–3842s) and 188–209 (4203–4470s). The speaker labels come from automatic diarisation. Where the label and the addressee disagree (e.g. 3788s is addressed as Ahwaz at 3842s), I cite the timestamp rather than a name.