fix(kong): add app-dev LAN origin (192.168.0.251:13000) to dev CORS

Completes the dev CORS origin set after an infra-drift reset discarded a local
kong.yml change (t_764c4b28). Tracked superset so a future kong reload can only
add origins, never drop a needed one. Live .94 kong unchanged (applies on next reload).

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

volumes/api/kong.yml

@@ -51,6 +51,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -95,6 +96,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -139,6 +141,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -185,6 +188,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -241,6 +245,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -297,6 +302,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -359,6 +365,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -414,6 +421,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -469,6 +477,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -516,6 +525,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
           methods:
             - GET
             - POST
@@ -596,6 +606,7 @@ services:
             - "http://192.168.0.74"
             - "http://localhost:3000"   # keep for local dev if needed
             - "http://localhost:5173"   # vite default
+            - "http://192.168.0.251:13000"   # app-dev LAN (deployed dev frontend)
             - "http://192.168.0.94:50001"
             - "http://192.168.0.74"
           methods:

volumes/db/cc/71-class-management.sql

@@ -0,0 +1,184 @@
+-- 71-class-management.sql
+-- Foundational: capture the (previously untracked) class-management schema and harden its RLS.
+--
+-- Background: `classes`, `class_teachers`, `class_students`, `enrollment_requests` existed only on
+-- live dev (.94), created out-of-band, with NO tracked DDL. Their schema/FKs/uniques are sound,
+-- but RLS exposed `class_students` / `class_teachers` to service_role ONLY — so any API path that
+-- calls Supabase AS THE USER (the correct, RLS-enforced pattern) reads ZERO rows. This migration:
+--   1. captures the real schema (idempotent; no-op on environments that already have it),
+--   2. adds SECURITY DEFINER membership helpers (avoid RLS recursion in policies),
+--   3. adds as-user RLS policies so teachers/admins/students can read rosters under RLS,
+--      while keeping the existing service_role policies intact.
+-- Verified against live .94 schema 2026-06-06 (all FKs/uniques/checks already present there).
+
+--==========================================================================================
+-- 1. Tables (idempotent capture for fresh environments; skipped where they already exist)
+--==========================================================================================
+
+create table if not exists public.classes (
+    id uuid primary key default gen_random_uuid(),
+    institute_id uuid not null references public.institutes(id) on delete cascade,
+    name varchar not null,
+    class_code text,
+    subject varchar,
+    key_stage text,
+    year_group varchar,
+    academic_year varchar,
+    description text,
+    type varchar not null default 'standard',
+    is_active boolean not null default true,
+    created_by uuid not null references public.profiles(id),
+    created_at timestamptz not null default now(),
+    updated_at timestamptz not null default now()
+);
+
+create table if not exists public.class_teachers (
+    id uuid primary key default gen_random_uuid(),
+    class_id uuid not null references public.classes(id) on delete cascade,
+    teacher_id uuid not null references public.profiles(id) on delete cascade,
+    is_primary boolean not null default false,
+    can_edit boolean not null default true,
+    assigned_at timestamptz not null default now(),
+    assigned_by uuid references public.profiles(id),
+    constraint class_teachers_class_id_teacher_id_key unique (class_id, teacher_id)
+);
+
+create table if not exists public.class_students (
+    id uuid primary key default gen_random_uuid(),
+    class_id uuid not null references public.classes(id) on delete cascade,
+    student_id uuid not null references public.profiles(id) on delete cascade,
+    status varchar not null default 'active'
+        check (status::text = any (array['active','inactive','pending'])),
+    enrolled_at timestamptz not null default now(),
+    enrolled_by uuid references public.profiles(id),
+    constraint class_students_class_id_student_id_key unique (class_id, student_id)
+);
+
+create table if not exists public.enrollment_requests (
+    id uuid primary key default gen_random_uuid(),
+    class_id uuid not null references public.classes(id) on delete cascade,
+    student_id uuid not null references public.profiles(id) on delete cascade,
+    status varchar not null default 'pending'
+        check (status::text = any (array['pending','approved','rejected'])),
+    request_message text,
+    requested_at timestamptz not null default now(),
+    responded_at timestamptz,
+    responded_by uuid references public.profiles(id),
+    response_message text
+);
+
+create index if not exists idx_classes_institute     on public.classes(institute_id);
+create index if not exists idx_classes_created_by     on public.classes(created_by);
+create index if not exists idx_classes_class_code     on public.classes(class_code);
+create index if not exists idx_class_teachers_class    on public.class_teachers(class_id);
+create index if not exists idx_class_students_class    on public.class_students(class_id);
+
+--==========================================================================================
+-- 2. SECURITY DEFINER membership helpers
+--    Run as owner (bypass RLS on the inner tables) → no policy recursion when referenced below.
+--==========================================================================================
+
+create or replace function public.user_institute_ids()
+returns setof uuid language sql stable security definer set search_path = public as $$
+  select institute_id from public.institute_memberships where profile_id = auth.uid()
+$$;
+
+create or replace function public.is_class_teacher(p_class uuid)
+returns boolean language sql stable security definer set search_path = public as $$
+  select exists (select 1 from public.class_teachers
+                 where class_id = p_class and teacher_id = auth.uid())
+$$;
+
+create or replace function public.is_class_admin(p_class uuid)
+returns boolean language sql stable security definer set search_path = public as $$
+  select exists (
+    select 1 from public.classes c
+    join public.institute_memberships m on m.institute_id = c.institute_id
+    where c.id = p_class and m.profile_id = auth.uid()
+      and m.role in ('school_admin','department_head'))
+$$;
+
+create or replace function public.is_institute_member_of_class(p_class uuid)
+returns boolean language sql stable security definer set search_path = public as $$
+  select exists (
+    select 1 from public.classes c
+    join public.institute_memberships m on m.institute_id = c.institute_id
+    where c.id = p_class and m.profile_id = auth.uid())
+$$;
+
+--==========================================================================================
+-- 3. RLS — enable + (re)declare every policy so this file is the source of truth.
+--    Existing service_role / institute_read / er_own policies are preserved verbatim;
+--    the cs_read/cs_write/ct_read/ct_write policies are the NEW as-user grants.
+--==========================================================================================
+
+alter table public.classes             enable row level security;
+alter table public.class_teachers      enable row level security;
+alter table public.class_students      enable row level security;
+alter table public.enrollment_requests enable row level security;
+
+-- classes ---------------------------------------------------------------------------------
+drop policy if exists classes_service_role on public.classes;
+create policy classes_service_role on public.classes
+  using (auth.role() = 'service_role');
+
+drop policy if exists classes_institute_read on public.classes;
+create policy classes_institute_read on public.classes for select to authenticated
+  using (institute_id in (select public.user_institute_ids()));
+
+-- NEW: teachers/admins of a class can update it; admins can insert/delete within their institute
+drop policy if exists classes_admin_write on public.classes;
+create policy classes_admin_write on public.classes for all to authenticated
+  using (institute_id in (select public.user_institute_ids())
+         and (public.is_class_admin(id) or public.is_class_teacher(id)))
+  with check (institute_id in (select public.user_institute_ids()));
+
+-- class_teachers --------------------------------------------------------------------------
+drop policy if exists ct_service_role on public.class_teachers;
+create policy ct_service_role on public.class_teachers
+  using (auth.role() = 'service_role');
+
+-- NEW: institute members can see who teaches a class; the teacher can see their own rows
+drop policy if exists ct_read on public.class_teachers;
+create policy ct_read on public.class_teachers for select to authenticated
+  using (teacher_id = auth.uid() or public.is_institute_member_of_class(class_id));
+
+-- NEW: only school admins assign/unassign teachers
+drop policy if exists ct_write on public.class_teachers;
+create policy ct_write on public.class_teachers for all to authenticated
+  using (public.is_class_admin(class_id))
+  with check (public.is_class_admin(class_id));
+
+-- class_students --------------------------------------------------------------------------
+drop policy if exists cs_service_role on public.class_students;
+create policy cs_service_role on public.class_students
+  using (auth.role() = 'service_role');
+
+-- NEW: a student sees their own enrolment; teachers/admins of the class see the roster
+drop policy if exists cs_read on public.class_students;
+create policy cs_read on public.class_students for select to authenticated
+  using (student_id = auth.uid()
+         or public.is_class_teacher(class_id)
+         or public.is_class_admin(class_id));
+
+-- NEW: teachers (can_edit) and admins of the class manage enrolments
+drop policy if exists cs_write on public.class_students;
+create policy cs_write on public.class_students for all to authenticated
+  using (public.is_class_teacher(class_id) or public.is_class_admin(class_id))
+  with check (public.is_class_teacher(class_id) or public.is_class_admin(class_id));
+
+-- enrollment_requests ---------------------------------------------------------------------
+drop policy if exists er_service_role on public.enrollment_requests;
+create policy er_service_role on public.enrollment_requests
+  using (auth.role() = 'service_role');
+
+drop policy if exists er_own on public.enrollment_requests;
+create policy er_own on public.enrollment_requests for all to authenticated
+  using (student_id = auth.uid())
+  with check (student_id = auth.uid());
+
+-- NEW: teachers/admins of the class can read + respond to requests for their class
+drop policy if exists er_class_staff on public.enrollment_requests;
+create policy er_class_staff on public.enrollment_requests for all to authenticated
+  using (public.is_class_teacher(class_id) or public.is_class_admin(class_id))
+  with check (public.is_class_teacher(class_id) or public.is_class_admin(class_id));

volumes/db/cc/72-exam-marker.sql

@@ -0,0 +1,238 @@
+-- 72-exam-marker.sql
+-- Exam-marker operational tables (Supabase = source of truth for geometry/marks/submissions).
+-- Neo4j cc.public.exams holds the knowledge graph; joined by shared UUIDs (see spec §2).
+--
+-- Authorization is owned by this layer: the exam API calls Supabase AS THE USER, so these RLS
+-- policies are enforced (service_role policies cover the Neo4j-projection / seed paths only).
+-- Depends on: 71-class-management.sql (marking_batches.class_id → classes; user_institute_ids()).
+
+--==========================================================================================
+-- 1. Tables
+--==========================================================================================
+
+create table if not exists public.exam_templates (
+    id uuid primary key default gen_random_uuid(),
+    exam_id uuid references public.eb_exams(id) on delete set null,      -- null for ad-hoc upload
+    exam_code text,                                                      -- denormalised → Neo4j join
+    institute_id uuid not null references public.institutes(id) on delete cascade,
+    teacher_id uuid not null references public.profiles(id) on delete cascade,
+    title text not null,
+    subject text,
+    source_file_id uuid references public.files(id) on delete set null, -- uploaded PDF (R2.2)
+    page_count int,
+    status text not null default 'draft' check (status in ('draft','ready','archived')),
+    created_at timestamptz not null default timezone('utc', now()),
+    updated_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.exam_questions (
+    id uuid primary key default gen_random_uuid(),
+    template_id uuid not null references public.exam_templates(id) on delete cascade,
+    parent_id uuid references public.exam_questions(id) on delete cascade,
+    label text not null,
+    "order" int not null default 0,
+    max_marks numeric not null default 0,
+    answer_type text check (answer_type in ('written','mcq','short','diagram')),
+    mcq_options jsonb,
+    mark_scheme jsonb not null default '{}'::jsonb,    -- MarkScheme union from exam-marker types.ts
+    is_container boolean not null default false,       -- true → Neo4j Question, false → Part
+    spec_ref text,                                     -- manual spec-point tag → ASSESSES (R3.5.3)
+    created_at timestamptz not null default timezone('utc', now()),
+    updated_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.exam_response_areas (
+    id uuid primary key default gen_random_uuid(),     -- == Neo4j Region.uuid_string
+    question_id uuid not null references public.exam_questions(id) on delete cascade,
+    template_id uuid not null references public.exam_templates(id) on delete cascade,  -- RLS denorm
+    page int not null,
+    bounds jsonb not null,                             -- {x,y,w,h}
+    kind text not null check (kind in ('response','context')),
+    response_form text check (response_form in
+        ('lines','answer-box','working','diagram','tick-boxes','table','blanks')),
+    source text not null default 'manual' check (source in ('manual','ai')),
+    confirmed boolean not null default true,
+    confidence numeric,
+    created_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.exam_boundaries (
+    id uuid primary key default gen_random_uuid(),
+    template_id uuid not null references public.exam_templates(id) on delete cascade,
+    question_id uuid references public.exam_questions(id) on delete set null,
+    label text,
+    page_index int not null,
+    y numeric not null,
+    bounds jsonb,
+    source text not null default 'manual' check (source in ('manual','ai')),
+    confirmed boolean not null default true,
+    created_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.marking_batches (
+    id uuid primary key default gen_random_uuid(),
+    template_id uuid not null references public.exam_templates(id) on delete cascade,
+    class_id uuid references public.classes(id) on delete set null,     -- roster via class_students
+    institute_id uuid not null references public.institutes(id) on delete cascade,
+    teacher_id uuid not null references public.profiles(id) on delete cascade,   -- batch owner (R2.4)
+    title text,
+    status text not null default 'open' check (status in ('open','marking','complete','archived')),
+    created_at timestamptz not null default timezone('utc', now()),
+    updated_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.student_submissions (
+    id uuid primary key default gen_random_uuid(),
+    batch_id uuid not null references public.marking_batches(id) on delete cascade,
+    student_id uuid references public.profiles(id) on delete set null,  -- null until matched
+    student_name text,
+    scan_file_id uuid references public.files(id) on delete set null,
+    scan_url text,
+    qr_code text,
+    matching_method text check (matching_method in ('ordered','ocr_name','qr_code','manual')),
+    match_confidence numeric,
+    page_start int,
+    page_count int,
+    status text not null default 'unmatched'
+        check (status in ('unmatched','matched','marking','complete','absent')),
+    annotation_snapshot jsonb,
+    created_at timestamptz not null default timezone('utc', now()),
+    updated_at timestamptz not null default timezone('utc', now())
+);
+
+create table if not exists public.mark_entries (
+    id uuid primary key default gen_random_uuid(),
+    submission_id uuid not null references public.student_submissions(id) on delete cascade,
+    question_id uuid not null references public.exam_questions(id) on delete cascade,
+    batch_id uuid not null references public.marking_batches(id) on delete cascade,  -- RLS denorm
+    awarded_marks numeric not null default 0,
+    mark_scheme_detail jsonb not null default '{}'::jsonb,
+    annotation_shape_ids jsonb not null default '[]'::jsonb,
+    comment text,
+    marked_by text not null default 'teacher' check (marked_by in ('teacher','ai')),
+    ai_confidence numeric,
+    confirmed boolean not null default true,
+    marked_at timestamptz not null default timezone('utc', now())
+);
+
+create index if not exists idx_exam_templates_institute on public.exam_templates(institute_id);
+create index if not exists idx_exam_templates_teacher    on public.exam_templates(teacher_id);
+create index if not exists idx_exam_questions_template    on public.exam_questions(template_id);
+create index if not exists idx_exam_questions_parent      on public.exam_questions(parent_id);
+create index if not exists idx_exam_regions_question      on public.exam_response_areas(question_id);
+create index if not exists idx_exam_regions_template      on public.exam_response_areas(template_id);
+create index if not exists idx_exam_boundaries_template   on public.exam_boundaries(template_id);
+create index if not exists idx_batches_template           on public.marking_batches(template_id);
+create index if not exists idx_batches_institute          on public.marking_batches(institute_id);
+create index if not exists idx_submissions_batch          on public.student_submissions(batch_id);
+create index if not exists idx_marks_submission           on public.mark_entries(submission_id);
+create index if not exists idx_marks_batch                on public.mark_entries(batch_id);
+
+--==========================================================================================
+-- 2. updated_at triggers (reuse public.handle_updated_at from 62-functions-triggers.sql)
+--==========================================================================================
+
+drop trigger if exists handle_exam_templates_updated_at on public.exam_templates;
+create trigger handle_exam_templates_updated_at before update on public.exam_templates
+  for each row execute function public.handle_updated_at();
+
+drop trigger if exists handle_exam_questions_updated_at on public.exam_questions;
+create trigger handle_exam_questions_updated_at before update on public.exam_questions
+  for each row execute function public.handle_updated_at();
+
+drop trigger if exists handle_marking_batches_updated_at on public.marking_batches;
+create trigger handle_marking_batches_updated_at before update on public.marking_batches
+  for each row execute function public.handle_updated_at();
+
+drop trigger if exists handle_student_submissions_updated_at on public.student_submissions;
+create trigger handle_student_submissions_updated_at before update on public.student_submissions
+  for each row execute function public.handle_updated_at();
+
+--==========================================================================================
+-- 3. RLS — every table: a service_role passthrough (Neo4j projection / seeds) + as-user policies
+--==========================================================================================
+
+alter table public.exam_templates       enable row level security;
+alter table public.exam_questions        enable row level security;
+alter table public.exam_response_areas   enable row level security;
+alter table public.exam_boundaries       enable row level security;
+alter table public.marking_batches       enable row level security;
+alter table public.student_submissions   enable row level security;
+alter table public.mark_entries          enable row level security;
+
+-- exam_templates -------------------------------------------------------------------------
+drop policy if exists exam_templates_service on public.exam_templates;
+create policy exam_templates_service on public.exam_templates using (auth.role() = 'service_role');
+drop policy if exists exam_templates_read on public.exam_templates;
+create policy exam_templates_read on public.exam_templates for select to authenticated
+  using (institute_id in (select public.user_institute_ids()));
+drop policy if exists exam_templates_write on public.exam_templates;
+create policy exam_templates_write on public.exam_templates for all to authenticated
+  using (teacher_id = auth.uid() and institute_id in (select public.user_institute_ids()))
+  with check (teacher_id = auth.uid() and institute_id in (select public.user_institute_ids()));
+
+-- exam_questions / exam_response_areas / exam_boundaries: cascade authz from owning template
+drop policy if exists exam_questions_service on public.exam_questions;
+create policy exam_questions_service on public.exam_questions using (auth.role() = 'service_role');
+drop policy if exists exam_questions_all on public.exam_questions;
+create policy exam_questions_all on public.exam_questions for all to authenticated
+  using (exists (select 1 from public.exam_templates t
+                 where t.id = exam_questions.template_id
+                   and t.institute_id in (select public.user_institute_ids())))
+  with check (exists (select 1 from public.exam_templates t
+                 where t.id = exam_questions.template_id and t.teacher_id = auth.uid()));
+
+drop policy if exists exam_regions_service on public.exam_response_areas;
+create policy exam_regions_service on public.exam_response_areas using (auth.role() = 'service_role');
+drop policy if exists exam_regions_all on public.exam_response_areas;
+create policy exam_regions_all on public.exam_response_areas for all to authenticated
+  using (exists (select 1 from public.exam_templates t
+                 where t.id = exam_response_areas.template_id
+                   and t.institute_id in (select public.user_institute_ids())))
+  with check (exists (select 1 from public.exam_templates t
+                 where t.id = exam_response_areas.template_id and t.teacher_id = auth.uid()));
+
+drop policy if exists exam_boundaries_service on public.exam_boundaries;
+create policy exam_boundaries_service on public.exam_boundaries using (auth.role() = 'service_role');
+drop policy if exists exam_boundaries_all on public.exam_boundaries;
+create policy exam_boundaries_all on public.exam_boundaries for all to authenticated
+  using (exists (select 1 from public.exam_templates t
+                 where t.id = exam_boundaries.template_id
+                   and t.institute_id in (select public.user_institute_ids())))
+  with check (exists (select 1 from public.exam_templates t
+                 where t.id = exam_boundaries.template_id and t.teacher_id = auth.uid()));
+
+-- marking_batches: read = same institute (colleagues), write = owning teacher (R2.4)
+drop policy if exists marking_batches_service on public.marking_batches;
+create policy marking_batches_service on public.marking_batches using (auth.role() = 'service_role');
+drop policy if exists marking_batches_read on public.marking_batches;
+create policy marking_batches_read on public.marking_batches for select to authenticated
+  using (institute_id in (select public.user_institute_ids()));
+drop policy if exists marking_batches_write on public.marking_batches;
+create policy marking_batches_write on public.marking_batches for all to authenticated
+  using (teacher_id = auth.uid() and institute_id in (select public.user_institute_ids()))
+  with check (teacher_id = auth.uid() and institute_id in (select public.user_institute_ids()));
+
+-- student_submissions: authz cascades from batch ownership
+drop policy if exists submissions_service on public.student_submissions;
+create policy submissions_service on public.student_submissions using (auth.role() = 'service_role');
+drop policy if exists submissions_all on public.student_submissions;
+create policy submissions_all on public.student_submissions for all to authenticated
+  using (exists (select 1 from public.marking_batches b
+                 where b.id = student_submissions.batch_id and b.teacher_id = auth.uid()))
+  with check (exists (select 1 from public.marking_batches b
+                 where b.id = student_submissions.batch_id and b.teacher_id = auth.uid()));
+
+-- mark_entries: teacher (batch owner) full; student may read their own marks (R1.5, UI deferred)
+drop policy if exists marks_service on public.mark_entries;
+create policy marks_service on public.mark_entries using (auth.role() = 'service_role');
+drop policy if exists marks_teacher_all on public.mark_entries;
+create policy marks_teacher_all on public.mark_entries for all to authenticated
+  using (exists (select 1 from public.marking_batches b
+                 where b.id = mark_entries.batch_id and b.teacher_id = auth.uid()))
+  with check (exists (select 1 from public.marking_batches b
+                 where b.id = mark_entries.batch_id and b.teacher_id = auth.uid()));
+drop policy if exists marks_student_read on public.mark_entries;
+create policy marks_student_read on public.mark_entries for select to authenticated
+  using (exists (select 1 from public.student_submissions s
+                 where s.id = mark_entries.submission_id and s.student_id = auth.uid()));

volumes/db/cc/73-exam-marker-regions.sql

@@ -0,0 +1,42 @@
+-- 73-exam-marker-regions.sql
+-- Extends the exam-marker physical model (72-exam-marker.sql) for the locked S4-9 shape taxonomy
+-- (user discussion 2026-06-06; see ~/cc/ideas/2026-06-06-s4-9-design-answers-from-transcript.md).
+--
+-- Taxonomy: Boundary carves main Questions; a teacher draws a box around each Part; inside a Part
+-- live bounded regions of several KINDS. "Band"/"span" are retired. Idempotent (IF NOT EXISTS /
+-- drop-and-re-add the named CHECK). Additive on top of 72; safe to re-run.
+
+--==========================================================================================
+-- 1. exam_questions: geometry for the drawn Part box (and optional main-question region)
+--==========================================================================================
+-- Geometry previously lived only on response areas. A Part is now a drawn box, so the question
+-- row carries its own bounds + page. Nullable: main questions are derived between their boundaries.
+alter table public.exam_questions add column if not exists bounds jsonb;   -- {x,y,w,h}
+alter table public.exam_questions add column if not exists page int;
+
+comment on column public.exam_questions.bounds is 'Drawn box for a Part (leaf); null for a derived main question';
+comment on column public.exam_questions.page is 'Page index the Part box sits on; null for derived main questions';
+
+--==========================================================================================
+-- 2. exam_response_areas: more region kinds + context differentiation
+--==========================================================================================
+-- v1 keeps one generic Context but plans subject-specific differentiation later
+-- (graph, chart, data_table, diagram, code_block, passage, …). Nullable now.
+alter table public.exam_response_areas add column if not exists context_type text;
+
+comment on column public.exam_response_areas.context_type is
+  'Optional Context differentiation (v1 generic); future: graph|chart|data_table|diagram|code_block|passage';
+
+-- Extend the kind enum. Region kinds now:
+--   response        - where the student writes (uses response_form)
+--   context         - stimulus the question/part draws on (uses context_type)
+--   question_number - bounds the printed label "01" / "2.1" (physical metadata for OCR/AI)
+--   mark_area       - bounds the printed marks "[2]" / "Total for Question X is N marks"
+--   reference       - formulae/data sheets, appendices the student uses (kept, NOT ignored)
+--   furniture       - margins, page numbers, blank space, decoration (explicitly excluded)
+alter table public.exam_response_areas drop constraint if exists exam_response_areas_kind_check;
+alter table public.exam_response_areas add constraint exam_response_areas_kind_check
+  check (kind in ('response','context','question_number','mark_area','reference','furniture'));
+
+comment on column public.exam_response_areas.kind is
+  'response|context|question_number|mark_area|reference|furniture (see 73-exam-marker-regions.sql)';

volumes/db/cc/74-exam-marker-layout.sql

@@ -0,0 +1,112 @@
+-- 74-exam-marker-layout.sql
+-- S5 exam-marker layout layer + AI/manual seam provenance.
+--
+-- Adds the per-page layout projection that the docling auto-map pipeline writes to, plus
+-- provenance columns needed to distinguish unconfirmed AI suggestions from teacher/manual data.
+-- Safe/idempotent on top of 72-exam-marker.sql + 73-exam-marker-regions.sql.
+
+--==========================================================================================
+-- 1. exam_template_layout: stage-2 per-page layout layer
+--==========================================================================================
+create table if not exists public.exam_template_layout (
+    id uuid primary key default gen_random_uuid(),
+    template_id uuid not null references public.exam_templates(id) on delete cascade,
+    page_index int not null,
+    role text,
+    margin_left numeric,
+    margin_right numeric,
+    margin_top numeric,
+    margin_bottom numeric,
+    margins_enabled boolean not null default true,
+    source text not null default 'manual' check (source in ('manual','ai')),
+    confirmed boolean not null default true,
+    confidence numeric,
+    derivation text,
+    meta jsonb not null default '{}'::jsonb,
+    created_at timestamptz not null default timezone('utc', now()),
+    updated_at timestamptz not null default timezone('utc', now()),
+    unique (template_id, page_index)
+);
+
+comment on table public.exam_template_layout is
+  'Per-template/per-page layout projection for exam-marker auto-map review: page role, nullable margins, provenance and future layout-profile metadata.';
+comment on column public.exam_template_layout.role is
+  'Page role such as cover|question|continuation|blank|appendix; nullable when unknown/manual draft.';
+comment on column public.exam_template_layout.meta is
+  'Forward-compatible layout metadata: columns, front-matter, appendix/blank markers, future profile_id linkage, etc.';
+
+create index if not exists idx_exam_template_layout_template on public.exam_template_layout(template_id);
+create index if not exists idx_exam_template_layout_source_confirmed on public.exam_template_layout(template_id, source, confirmed);
+
+drop trigger if exists handle_exam_template_layout_updated_at on public.exam_template_layout;
+create trigger handle_exam_template_layout_updated_at before update on public.exam_template_layout
+  for each row execute function public.handle_updated_at();
+
+--==========================================================================================
+-- 2. AI seam/provenance columns on existing physical template tables
+--==========================================================================================
+-- Existing/manual rows default to authoritative manual data. Auto-map rows must explicitly set
+-- source='ai' and confirmed=false so reruns can refresh only unconfirmed AI suggestions.
+alter table public.exam_questions add column if not exists source text not null default 'manual' check (source in ('manual','ai'));
+alter table public.exam_questions add column if not exists confirmed boolean not null default true;
+alter table public.exam_questions add column if not exists confidence numeric;
+alter table public.exam_questions add column if not exists derivation text;
+
+alter table public.exam_response_areas add column if not exists mark_subtype text;
+alter table public.exam_response_areas add column if not exists derivation text;
+
+alter table public.exam_response_areas drop constraint if exists exam_response_areas_mark_subtype_check;
+alter table public.exam_response_areas add constraint exam_response_areas_mark_subtype_check
+  check (
+    mark_subtype is null
+    or (kind = 'mark_area' and mark_subtype in ('part_marks','question_total','grader_box'))
+  );
+
+alter table public.exam_boundaries add column if not exists confidence numeric;
+alter table public.exam_boundaries add column if not exists derivation text;
+
+-- Confidence values are normalized model/layout confidence scores when present.
+alter table public.exam_template_layout drop constraint if exists exam_template_layout_confidence_check;
+alter table public.exam_template_layout add constraint exam_template_layout_confidence_check
+  check (confidence is null or (confidence >= 0 and confidence <= 1));
+alter table public.exam_questions drop constraint if exists exam_questions_confidence_check;
+alter table public.exam_questions add constraint exam_questions_confidence_check
+  check (confidence is null or (confidence >= 0 and confidence <= 1));
+alter table public.exam_response_areas drop constraint if exists exam_response_areas_confidence_check;
+alter table public.exam_response_areas add constraint exam_response_areas_confidence_check
+  check (confidence is null or (confidence >= 0 and confidence <= 1));
+alter table public.exam_boundaries drop constraint if exists exam_boundaries_confidence_check;
+alter table public.exam_boundaries add constraint exam_boundaries_confidence_check
+  check (confidence is null or (confidence >= 0 and confidence <= 1));
+
+comment on column public.exam_questions.derivation is
+  'Lightweight provenance such as ai|manual|boundary_derived|margin_derived|layout_derived.';
+comment on column public.exam_response_areas.mark_subtype is
+  'Only meaningful for kind=mark_area: part_marks|question_total|grader_box. Grader boxes are persisted only when explicitly detected/provided.';
+comment on column public.exam_response_areas.derivation is
+  'Lightweight provenance such as ai|manual|detected|layout_derived.';
+comment on column public.exam_boundaries.derivation is
+  'Lightweight provenance such as ai|manual|band_derived.';
+
+create index if not exists idx_exam_questions_source_confirmed on public.exam_questions(template_id, source, confirmed);
+create index if not exists idx_exam_regions_source_confirmed on public.exam_response_areas(template_id, source, confirmed);
+create index if not exists idx_exam_boundaries_source_confirmed on public.exam_boundaries(template_id, source, confirmed);
+
+--==========================================================================================
+-- 3. RLS — mirror exam_templates ownership/institute scope via the owning template
+--==========================================================================================
+alter table public.exam_template_layout enable row level security;
+
+drop policy if exists exam_template_layout_service on public.exam_template_layout;
+create policy exam_template_layout_service on public.exam_template_layout
+  using (auth.role() = 'service_role');
+
+drop policy if exists exam_template_layout_all on public.exam_template_layout;
+create policy exam_template_layout_all on public.exam_template_layout for all to authenticated
+  using (exists (select 1 from public.exam_templates t
+                 where t.id = exam_template_layout.template_id
+                   and t.institute_id in (select public.user_institute_ids())))
+  with check (exists (select 1 from public.exam_templates t
+                 where t.id = exam_template_layout.template_id
+                   and t.teacher_id = auth.uid()
+                   and t.institute_id in (select public.user_institute_ids())));

volumes/db/cc/75-files-gc-protect-delete.sql

@@ -0,0 +1,51 @@
+-- Allow our file-GC helpers to satisfy Supabase storage.protect_delete
+-- without weakening the managed storage trigger for arbitrary raw deletes.
+--
+-- Supabase storage.protect_delete permits direct storage.objects deletes only
+-- when the transaction-local GUC storage.allow_delete_query is 'true'.  The
+-- Storage API sets that GUC for its own deletes.  public.files GC is our
+-- trigger-side cleanup path, so scope the same GUC to these SECURITY DEFINER
+-- helpers immediately before their storage.objects DELETE statements.
+
+create or replace function public._delete_storage_objects(p_bucket text, p_path text)
+returns void
+language plpgsql security definer
+set search_path to 'public', 'storage'
+as $$
+begin
+  if p_bucket is null or p_path is null then
+    return;
+  end if;
+
+  perform set_config('storage.allow_delete_query', 'true', true);
+
+  delete from storage.objects where bucket_id = p_bucket and name = p_path;
+  delete from storage.objects where bucket_id = p_bucket and name like p_path || '/%';
+end
+$$;
+
+create or replace function public._delete_storage_prefix(p_bucket text, p_prefix text)
+returns void
+language plpgsql security definer
+set search_path to 'public', 'storage'
+as $$
+begin
+  if p_bucket is null or p_prefix is null then
+    return;
+  end if;
+
+  perform set_config('storage.allow_delete_query', 'true', true);
+
+  -- Delete any objects whose name starts with the prefix + '/'
+  delete from storage.objects where bucket_id = p_bucket and name like p_prefix || '/%';
+  -- In case an object exists exactly at the prefix (rare but safe)
+  delete from storage.objects where bucket_id = p_bucket and name = p_prefix;
+end
+$$;
+
+-- These helpers are intended for our SECURITY DEFINER GC triggers/helpers, not
+-- as a general SQL delete API for application roles.
+revoke all on function public._delete_storage_objects(text, text) from public;
+revoke all on function public._delete_storage_prefix(text, text) from public;
+revoke all on function public._delete_storage_objects(text, text) from anon, authenticated;
+revoke all on function public._delete_storage_prefix(text, text) from anon, authenticated;

volumes/db/cc/76-cabinet-memberships-rls-definer.sql

@@ -0,0 +1,88 @@
+-- 76-cabinet-memberships-rls-definer.sql
+-- Fix cabinet_memberships/file_cabinets/files recursive RLS.
+--
+-- The original cabinet membership policies joined file_cabinets directly, while
+-- the file_cabinets/files membership policies joined cabinet_memberships
+-- directly. Under an authenticated as-user SELECT this creates an RLS cycle and
+-- PostgreSQL raises 42P17 (infinite recursion detected in policy for relation
+-- "cabinet_memberships").
+--
+-- SECURITY DEFINER helpers evaluate the ownership/membership checks as the
+-- function owner (bypassing the inner RLS checks) while still keying the result
+-- to auth.uid(), matching the class-management helper pattern in 71.
+--
+-- The existing public tables are owned by supabase_admin; Supabase migrations
+-- run as that table owner on dev/prod so the SECURITY DEFINER helpers are owned
+-- by a role that bypasses the inner RLS checks.
+
+create or replace function public.is_cabinet_owner(p_cabinet uuid)
+returns boolean
+language sql
+stable
+security definer
+set search_path = public
+as $$
+  select exists (
+    select 1
+    from public.file_cabinets c
+    where c.id = p_cabinet
+      and c.user_id = auth.uid()
+  )
+$$;
+
+create or replace function public.is_cabinet_member(p_cabinet uuid)
+returns boolean
+language sql
+stable
+security definer
+set search_path = public
+as $$
+  select exists (
+    select 1
+    from public.cabinet_memberships m
+    where m.cabinet_id = p_cabinet
+      and m.profile_id = auth.uid()
+  )
+$$;
+
+-- Keep function execution available to the roles used by RLS policies. The
+-- functions disclose only a boolean about the caller's own auth.uid() state.
+revoke all on function public.is_cabinet_owner(uuid) from public, anon;
+revoke all on function public.is_cabinet_member(uuid) from public, anon;
+grant execute on function public.is_cabinet_owner(uuid) to authenticated, service_role;
+grant execute on function public.is_cabinet_member(uuid) to authenticated, service_role;
+
+-- Re-declare cabinet_memberships policies without direct file_cabinets subqueries.
+drop policy if exists cm_read_self_or_owner on public.cabinet_memberships;
+create policy cm_read_self_or_owner on public.cabinet_memberships
+  for select to authenticated
+  using (profile_id = auth.uid() or public.is_cabinet_owner(cabinet_id));
+
+drop policy if exists cm_insert_by_owner on public.cabinet_memberships;
+create policy cm_insert_by_owner on public.cabinet_memberships
+  for insert to authenticated
+  with check (public.is_cabinet_owner(cabinet_id));
+
+drop policy if exists cm_update_by_owner on public.cabinet_memberships;
+create policy cm_update_by_owner on public.cabinet_memberships
+  for update to authenticated
+  using (public.is_cabinet_owner(cabinet_id))
+  with check (public.is_cabinet_owner(cabinet_id));
+
+drop policy if exists cm_delete_by_owner on public.cabinet_memberships;
+create policy cm_delete_by_owner on public.cabinet_memberships
+  for delete to authenticated
+  using (public.is_cabinet_owner(cabinet_id));
+
+-- Re-declare membership-based cabinet/file read policies without direct
+-- cabinet_memberships subqueries, so selecting cabinets/files does not recurse
+-- back through cabinet_memberships RLS.
+drop policy if exists "User can access cabinets via membership" on public.file_cabinets;
+create policy "User can access cabinets via membership" on public.file_cabinets
+  for select to authenticated
+  using (public.is_cabinet_member(id));
+
+drop policy if exists "User can access files via membership" on public.files;
+create policy "User can access files via membership" on public.files
+  for select to authenticated
+  using (public.is_cabinet_member(cabinet_id));

volumes/db/cc/77-storage-taxonomy-rls.sql

@@ -0,0 +1,108 @@
+-- 77-storage-taxonomy-rls.sql
+-- RLS for taxonomy storage buckets.  cc.examboards intentionally gets no
+-- direct object-read policy: exam-board PDF bytes are exposed only through
+-- API-issued, time-limited signed URLs.
+
+create or replace function public.is_platform_admin()
+returns boolean
+language sql
+stable
+security definer
+set search_path = public
+as $$
+  select exists (
+    select 1
+    from public.admin_profiles ap
+    where ap.id = auth.uid()
+      and coalesce(ap.is_super_admin, false)
+  )
+$$;
+
+grant execute on function public.is_platform_admin() to authenticated;
+
+-- Use the first path segment of cc.institutes objects as the institute id.
+-- Expected layout: cc.institutes/<institute_uuid>/...
+create or replace function public.storage_object_first_path_segment_uuid(p_name text)
+returns uuid
+language plpgsql
+immutable
+as $$
+declare
+  v_segment text;
+begin
+  v_segment := split_part(coalesce(p_name, ''), '/', 1);
+  if v_segment = '' then
+    return null;
+  end if;
+  begin
+    return v_segment::uuid;
+  exception when invalid_text_representation then
+    return null;
+  end;
+end;
+$$;
+
+grant execute on function public.storage_object_first_path_segment_uuid(text) to authenticated;
+
+-- Exam-board catalogue rows are authenticated metadata.  PDF bytes remain
+-- protected separately in cc.examboards storage and require signed URLs.
+alter table public.eb_specifications enable row level security;
+alter table public.eb_exams enable row level security;
+
+drop policy if exists "eb_specifications read by authenticated users" on public.eb_specifications;
+drop policy if exists "eb_exams read by authenticated users" on public.eb_exams;
+
+create policy "eb_specifications read by authenticated users"
+  on public.eb_specifications
+  for select
+  to authenticated
+  using (true);
+
+create policy "eb_exams read by authenticated users"
+  on public.eb_exams
+  for select
+  to authenticated
+  using (true);
+
+alter table storage.objects enable row level security;
+
+-- Replace only the policies owned by this migration.  The absence of any
+-- cc.examboards SELECT policy is the deny-by-default posture for raw reads.
+drop policy if exists "cc.admin objects read by platform admins" on storage.objects;
+drop policy if exists "cc.institutes objects read by institute members" on storage.objects;
+drop policy if exists "cc.public objects read by authenticated users" on storage.objects;
+
+create policy "cc.admin objects read by platform admins"
+  on storage.objects
+  for select
+  to authenticated
+  using (
+    bucket_id = 'cc.admin'
+    and public.is_platform_admin()
+  );
+
+create policy "cc.institutes objects read by institute members"
+  on storage.objects
+  for select
+  to authenticated
+  using (
+    bucket_id = 'cc.institutes'
+    and public.storage_object_first_path_segment_uuid(name) in (
+      select public.user_institute_ids()
+    )
+  );
+
+create policy "cc.public objects read by authenticated users"
+  on storage.objects
+  for select
+  to authenticated
+  using (bucket_id = 'cc.public');
+
+comment on policy "cc.admin objects read by platform admins" on storage.objects
+  is 'cc.admin raw reads are limited to platform admins.';
+comment on policy "cc.institutes objects read by institute members" on storage.objects
+  is 'cc.institutes raw reads are scoped by leading path segment institute uuid.';
+comment on policy "cc.public objects read by authenticated users" on storage.objects
+  is 'cc.public raw reads are available to any authenticated user.';
+comment on table storage.objects
+  is 'Classroom Copilot taxonomy buckets use RLS; cc.examboards raw reads stay denied and are served through API signed URLs.';