This document is the long-running implementation brief for turning
the local scheduling logic into a modern exams operations platform hosted at
exams.twisteredu.co.uk. It defines the product,
inputs, rules, outputs, architecture, UI, phases, and acceptance
checks needed to build the tool without losing the logic that
already works.
The app should guide users through one clear workflow while still allowing advanced configuration when needed.
The core input is not a resit. The core input is that a pupil needs to take an exam, assessment, unit, component, or paper. Resit entry management is one workflow inside the wider platform.
Build the schedule from school criteria: dates, sessions, rooms, access arrangements, invigilation, year restrictions, and clash rules.
Import or enter the exam board timetable, match it to pupil entries, detect clashes and heavy days, then publish personalised schedules.
Import results, unit/module marks, UMS, boundaries, grades, services requested, returned scripts, and resit entry decisions.
The web app must be adaptable. Every input below should be either imported, manually editable, or configurable at project level.
| Area | Fields | Required | Purpose |
|---|---|---|---|
| Students | Candidate ID, name, year group, email, parent emails | Candidate ID, name, year group | Identity, student timetables, mail merge, year restrictions. |
| Pupil exam requirements | Candidate ID, subject, assessment/component/paper, exam type, confirmed flag, source | Candidate ID, subject, assessment/component/paper | One row means one pupil needs one exam sitting. |
| Assessments | Year group, subject, assessment name, duration, qualification, component code | Year group, subject, assessment name, duration | Defines the schedulable exam groups. |
| Access arrangements | Candidate number(s), extra time, separate room type, rest breaks, laptop, reader, scribe, notes | Candidate number(s), arrangement type | Controls finish times, room allocation, supervision, and invigilation instructions. |
| Teachers | Teacher name, subject, unavailable dates, department, role, support allowance | Optional | Supports teacher spread, invigilation eligibility, and pupil-support protection. |
| Teacher timetable | Teacher, day, period, class, subject, year group, room, available flag | Required for invigilation scheduling | Determines whether a teacher is free or whether a lesson can be repurposed for invigilation. |
| Invigilators | Name, internal/external, availability, max periods, room restrictions, training status | Required for invigilation scheduling | Allocates invigilators using configurable ratios and override rules. |
| Rooms | Room name, capacity, available dates, access suitability, location | Optional for phase 1 | Allows location allocation and room pressure reports. |
| Days | Date, label, allowed year groups, capacity target, hard capacity | Yes | Defines the exam window and day-specific rules. |
| Sessions | Name, start, end, hard end, capacity, overflow flag, clash flag | Yes | Defines timetable blocks and fallback patterns. |
| Constraints | Scope, type, hard/soft, weight, description | Yes | Makes the optimiser configurable rather than hard-coded. |
| Scenarios | Name, session set, ruleset, weights, seed, iterations | Yes | Allows comparison between planning options. |
| Exam board timetable | Board, qualification, subject, component, date, start, duration | For external exams | External schedules are entered, not optimised, then matched to pupil entries. |
| Pupil subject entries | Candidate ID, qualification, board, subject, unit/component, HOD confirmation | For external exams | Allows HODs to confirm or update external exam entries. |
| Results | Candidate ID, board, subject, unit, raw mark, UMS, grade, overall grade | For results hub | Stores external results, including module/unit results and overall awards. |
| Grade boundaries | Board, series, subject, unit, grade, raw mark boundary, UMS boundary | For results hub | Supports analysis of marks, grades, near misses, and resit decisions. |
| Exam services | Candidate ID, paper, service type, status, cost, deadline, uploaded script | For post-results services | Tracks reviews of marking, clerical checks, access to scripts, and returned papers. |
| Policies | Policy name, category, file, version, effective date, access level | For policy centre | Stores secure exam policies and links them to guidance and workflows. |
| Mail merge templates | Template name, type, audience, variables, attachment rules | For communication | Generates timetable PDFs, results letters, resit letters, and exam-service notices. |
Rules should be stored as configurable records. The app must make it clear which rules are hard blockers and which are weighted preferences.
Cannot be broken unless the schedule is declared infeasible.
Can be broken with a visible penalty and exception report.
Weights let the user decide which compromises are least acceptable.
Access arrangements must be first-class scheduling inputs, not notes. They affect rooming, timings, supervision, instructions, and invigilation.
Support multiple arrangements per pupil and per exam where needed.
Extra rooms can be assigned to external invigilators by default, with configurable exceptions.
Invigilators should see exactly what applies in their room, including what-if guidance.
Invigilation should be scheduled after the exam timetable and room allocation exist. The system must support internal teachers, external invigilators, ratios, override options, and teacher timetable imports.
| Individual room | Can be 1:1 where required. |
| Small group room | Configurable ratio, often external invigilator led. |
| Large space | Default configurable rule: 1:30 plus 1. |
| Override | Manual override with reason and audit log. |
The platform should continue after exams. Results, grade boundaries, services, scripts, and resit entries should live in one controlled workflow rather than email chains.
Import external results, module/unit results, raw marks, UMS, grade boundaries, and overall grades.
Use module results to identify possible resits, collect decisions, and prepare exam board entry data.
Track reviews of marking, clerical checks, access to scripts, return of papers, costs, deadlines, and uploaded scripts.
Mail merge, PDF generation, secure policy storage, and role-based guidance should be part of the platform from the beginning.
Generate personalised PDFs and emails for different exam workflows.
Store exam policies securely, version them, and link them to invigilator guides, exam services, access arrangements, and results workflows.
The platform needs role-based access from the start. Exam managers, leadership, HODs, HOYs, teachers, students, and parents should see different information and have different actions available.
| Role | Typical Access | Allowed Actions | Restrictions |
|---|---|---|---|
| Exam Manager | Full operational access across projects, schedules, entries, rooms, invigilation, results, services, policies, and communications. | Create projects, import data, configure rules, run schedules, publish outputs, manage services, upload results, manage users. | Actions should be audited because this role can change official exam data. |
| Leadership | Read-only or approval-level access to dashboards, high-level schedules, risks, capacity, results summaries, and policy compliance. | Review status, approve publication, view reports, monitor exceptions. | Should not edit operational schedule data by default. |
| HOD | Department-level access to subject entries, results, scripts, services, and subject-specific schedules. | Confirm entries, review returned papers, request services, comment on resit decisions, view subject candidate lists. | Cannot view unrelated departments unless granted wider access. |
| HOY | Year-group access to pupil timetables, clashes, access risks, heavy days, attendance concerns, and communication status. | Review year group impact, support student follow-up, view relevant student schedules. | No default access to department-only scripts or whole-school configuration. |
| Teacher | Own classes, assigned invigilation, relevant scripts/papers, relevant candidate lists, and guidance. | View invigilation instructions, view assigned rooms, view returned scripts when permitted, comment on services where assigned. | Should not see unrelated pupil results or full platform settings. |
| Student | Own timetable, room, access arrangements that apply to them, results letters, exam-service status where appropriate. | Download timetable, view personalised notices, acknowledge communications if enabled. | Can only see their own data. |
| Parent | Linked child timetable, letters, results communications, service request status where permitted. | View/download published documents, acknowledge communications, submit consent/confirmation if enabled. | Can only see linked children and published information. |
| External Invigilator | Assigned sessions, room cards, candidate lists, access instructions, invigilation guide, policies needed for the assignment. | View assigned duties, mark checklist complete, report incidents if enabled. | No access to wider student records, results, or internal planning beyond assigned duties. |
| System Admin | Technical administration, integrations, backups, domain settings, and user provisioning. | Manage authentication, roles, deployment settings, backups, and logs. | Should not be used for everyday exam operations. |
The first production version should reuse the existing Python scheduling logic while introducing a web UI, persisted projects, background runs, and reproducible exports.
| Frontend | Next.js, React, TypeScript |
| Backend API | FastAPI, Python |
| Optimiser | Current Python scheduler, later OR-Tools if needed |
| Database | PostgreSQL |
| Jobs | Redis plus RQ or Celery |
| Deployment | Docker Compose on VPS |
| Proxy | Caddy or Nginx with HTTPS |
apps/web | Next.js app and UI components |
apps/api | FastAPI app, routes, auth, exports |
packages/scheduler | Ported scheduling engine and tests |
packages/shared | Shared schemas and generated API client |
infra | Docker, Caddy/Nginx, deployment scripts |
docs | Product specs, import templates, admin guide |
FastAPI is a Python web framework for building the backend API. In this architecture, the browser UI calls FastAPI endpoints to upload files, save settings, start scheduler jobs, fetch timetable data, and download exports. It fits well because the optimiser and Excel/PDF generation are already Python-friendly.
SQLite is fine for a local single-user tool or an early prototype. PostgreSQL is the better production choice for a VPS web app with multiple users, background jobs, uploaded files, audit history, concurrent scenario runs, results data, and policy storage.
The model must separate exam definitions, pupil exam requirements, scheduled exam blocks, individual pupil sittings, room allocation, and invigilation. Resits are not the core object; they are a type of pupil exam requirement.
| Table | Key Fields | Notes |
|---|---|---|
| users | id, name, email, auth_provider, status, last_login | All people who can log in: staff, students, parents, invigilators, and admins. |
| roles | id, name, description, system_role | Exam Manager, Leadership, HOD, HOY, Teacher, Student, Parent, External Invigilator, System Admin. |
| permissions | id, key, description, module, action | Granular capabilities such as schedule.run, results.view, services.request, policies.manage. |
| role_permissions | role_id, permission_id | Default permission set for each role. |
| user_roles | user_id, role_id, scope_type, scope_id | Assigns roles with scope, such as HOD for Economics or HOY for Year 11. |
| student_guardians | student_id, user_id, relationship, priority | Links parents/guardians to students. |
| teacher_classes | staff_id, class_id, subject, year_group | Links teachers to the pupils/classes they teach. |
| projects | id, name, school, academic_year, timezone, mode | Top-level container for an internal exam window, external exam series, results cycle, or services workflow. |
| exam_series | project_id, board, qualification, series, internal_external | Groups exams by internal window or external board series. |
| students | project_id, candidate_id, name, year_group, emails | Candidate ID should be unique within a project. |
| exam_components | series_id, year_group, subject, component_name, code, duration | Internal assessment, external paper, unit, module, or component definition. |
| pupil_exam_requirements | student_id, component_id, source, confirmed, entry_status | One row per pupil exam that must be sat, whether internal, external, resit, or evidence assessment. |
| access_arrangements | student_id, arrangement_type, value, applies_to, notes | Supports extra time, rest breaks, separate rooms, laptop, reader, scribe, prompter, and future arrangements. |
| exam_days | project_id, date, label, capacity_target, restrictions | Supports different windows and day-specific capacity. |
| exam_sessions | day_id, name, start, end, hard_end, capacity | Session patterns are configurable. |
| rooms | project_id, name, capacity, room_type, access_suitable | Large spaces, small group rooms, individual rooms, laptop rooms, and overflow rooms. |
| room_allocations | run_id, session_id, room_id, component_id, student_group | Maps exams and access-arrangement groups to locations. |
| constraints | scope, rule_type, hard, weight, config_json | Defines rules without code changes. |
| scenarios | project_id, name, session_set, weights, status | Three-session, four-block, or custom. |
| scenario_runs | scenario_id, seed, iterations, score, metrics_json | Stores reproducible optimisation results. |
| scheduled_exam_blocks | run_id, component_id, session_id | The main exam timetable. |
| student_sittings | run_id, student_id, component_id, session_id, room_id, sitting_type | Individual pupil timetable, including deferred clash sittings. |
| staff | project_id, name, email, subject, role, internal_external | Teachers, exams staff, HODs, and external invigilators. |
| teacher_timetable | staff_id, day, period, class_name, subject, year_group, room | Imported timetable used to determine teacher availability and lesson-release rules. |
| invigilation_rules | project_id, room_type, ratio, plus_one, override_allowed | Configurable ratios such as 1:1 or 1:30 plus 1. |
| invigilation_assignments | run_id, staff_id, room_allocation_id, role, override_reason | Who invigilates each room and why they were eligible. |
| exceptions | run_id, type, severity, student_id, component_id, detail | Clashes, back-to-back, over-capacity, missing data, access risks, invigilation gaps. |
| exam_board_schedules | series_id, board, subject, component, date, start, duration | External exam schedules entered from boards. |
| exam_entries | student_id, series_id, subject, component, status, hod_confirmed | External entry management and HOD confirmation. |
| results | student_id, component_id, raw_mark, ums, grade, overall_grade | Unit/module and overall results. |
| grade_boundaries | series_id, component_id, grade, raw_boundary, ums_boundary | Boundary storage for results analysis. |
| exam_services | student_id, component_id, service_type, status, cost, deadline | Review of marking, clerical checks, access to scripts, return of papers. |
| uploaded_scripts | exam_service_id, file_path, uploaded_by, uploaded_at | Returned papers/scripts viewable securely by teachers and HODs. |
| policies | name, category, file_path, version, access_level | Secure exam policy centre with versioned documents. |
| mail_merge_templates | name, type, audience, body, variables_json | PDF/email templates for timetables, results, resits, services, and invigilation. |
| exports | run_id, type, file_path, created_at | Excel, CSV, PDF, mail merge data. |
| audit_events | user_id, action, entity_type, entity_id, before_json, after_json, created_at | Records sensitive changes to schedules, entries, results, services, policies, and permissions. |
The UI should feel like a clean operational planning tool. Dense, visual, readable, and built for repeated review rather than a marketing site.
Recent projects, active scenarios, data warnings, last run status, and quick actions.
Upload files, map columns, preview detected data, and fix import issues before scheduling.
Create editable day/session patterns, including capacity, hard end times, and overflow rules.
Whole-school, year-group, student, teacher, and room views with capacity bars and warnings.
Manage extra time, separate rooms, rest breaks, laptop use, readers, scribes, and room-specific instructions.
Schedule internal teachers and external invigilators using ratios, teacher timetables, release rules, and overrides.
Compare three-session, four-block, and custom scenarios side by side with movement reports.
Review same-session clashes, deferred sittings, short gaps, over-capacity sessions, and data issues.
Import results, units, UMS, boundaries, and overall grades, then identify resit and service opportunities.
Track reviews of marking, access to scripts, returned papers, uploaded scripts, costs, status, and deadlines.
Store secure exam policies, versioned guidance, invigilator instructions, and role-based references.
Generate Excel workbooks, CSV files, student PDFs, teacher schedules, room schedules, and mail merge data.
Outputs should be generated per scenario run and remain attached to the run so decisions can be audited later.
Whole-school timetable, year-group grids, room grids, teacher grids, session load report, capacity report.
Student timetable table, student timetable grid, PDF timetable, future protected link and calendar export.
Clashes, deferred sittings, back-to-back, short gaps, extra-time finish risks, missing data, over-capacity sessions.
Invigilator timetable, room cards, attendance lists, access arrangement instructions, ratio checks, and override report.
Results PDFs, unit/module summaries, grade boundary analysis, resit recommendation lists, and HOD review exports.
Review of marking tracker, return of paper tracker, uploaded script links, service letters, and deadline reports.
This phased plan is designed for a long-running Codex task. Each phase has a clear done condition and should leave the app usable.
The deployment should be boring and recoverable. The app will be hosted on a VPS under the Twister Education domain.
web: Next.js appapi: FastAPI backendworker: scheduler and export jobspostgres: persistent databaseredis: background job queueproxy: HTTPS reverse proxy.env.example.These checks define what a usable first release must do before it can replace the local Python tool for real scheduling work.
| Area | Acceptance Check | Evidence |
|---|---|---|
| Import | Can import pupil exam requirements, exam components, access arrangements, rooms, teacher timetables, and external exam board schedules. | Successful import plus data issue report. |
| Scheduling | Can generate three-session and four-block internal exam schedules from the same project. | Scenario runs stored with metrics. |
| Comparison | Can compare both schedules side by side and list changed assessment placements. | Comparison page and export. |
| Visual timetable | Can review whole-school, year-group, and student views in the browser. | Browser verified at desktop and mobile widths. |
| Access arrangements | Can model extra time, separate rooms, rest breaks, laptop use, and room-specific instructions. | Access view, rooming report, and invigilator instructions. |
| Invigilation | Can allocate internal teachers and external invigilators using configurable ratios, availability, and override rules. | Invigilation timetable, ratio report, and override audit. |
| External exams | Can import exam board schedules, match them to pupil entries, and flag clashes and days over six hours. | External timetable view and exception report. |
| Results hub | Can store results, module/unit marks, UMS, grade boundaries, and overall grades. | Results import, results tables, and boundary analysis. |
| Exam services | Can track review of marking, return of paper, access to scripts, uploaded scripts, status, deadlines, and costs. | Services tracker and secure script viewer. |
| Policies | Can store secure, versioned exam policies and reference them from invigilation and services workflows. | Policy centre with role-based access. |
| Mail merge | Can generate PDFs and mail merge outputs for timetables, results, resits, services, and invigilation schedules. | Generated PDF/CSV outputs with template variables. |
| Permissions | Can enforce different access for exam managers, leadership, HODs, HOYs, teachers, students, parents, external invigilators, and admins. | Role tests and scoped visibility checks. |
| Audit | Can record who changed schedules, entries, results, services, policies, and permissions. | Audit event table and UI history view. |
| Exceptions | Can identify clashes, deferred sittings, back-to-back rows, access risks, invigilation gaps, and over-capacity sessions. | Exception queue matches exported reports. |
| Exports | Can export the current workbook outputs plus room, invigilation, services, and results outputs. | Generated files open without errors. |
| Deployment | Can deploy from GitHub to the VPS with documented commands. | Live app at exams.twisteredu.co.uk. |
Use this brief when starting the implementation task. It keeps the build aligned with the product goal and prevents drifting into a one-off prototype.