Add class markbook schema

Additive/idempotent columns carrying the two fields analyse.py v2 now emits but
v1 dropped: command_word (primary command verb per part) and preamble (stem
prose jsonb sidecar). Applied to dev .94.

Co-Authored-By: Claude Opus 4.8 <[email protected]>
Claude-Session: https://claude.ai/code/session_01GruxHXxfdp4kZCgAMVFgvV

volumes/db/cc/78-exam-marker-command-preamble.sql

@@ -0,0 +1,20 @@
+--==========================================================================================
+-- 78. exam_questions.command_word + preamble — richer recognition carried by the analyse contract v2 (WS-2)
+--==========================================================================================
+-- The extraction service (docling-exam-spike analyse.py v2, scripts/ANALYSE-CONTRACT.md) now carries two
+-- fields the thin v1 bridge dropped:
+--   * command_word — the primary command verb per part (calculate / explain / suggest / describe …), from
+--     structure.command_words / part_info.primary. Drives the question-tree command pill + future filtering.
+--   * preamble    — the stem prose that introduces a question/part (structure.preamble_by_label), as a
+--     jsonb sidecar {n, text:[…lines], nontext:[{kind,cls,page}], bands:[{page,y0,y1}]}. Kept as jsonb (not
+--     a column per line) so the tree/doc view can show the stem and the canvas can locate its band. The
+--     preamble band is ALSO emitted as a drawable context region (context_type='preamble'); this column is
+--     the authoritative text for the outline view.
+-- Both nullable; containers/parts without a detected command/preamble stay null. Additive + idempotent.
+alter table public.exam_questions add column if not exists command_word text;
+alter table public.exam_questions add column if not exists preamble jsonb;
+
+comment on column public.exam_questions.command_word is
+  'Primary command verb for a leaf part (calculate/explain/suggest/describe/…), from the analyse contract v2 (structure command_words / part_info). Null for container questions or when none detected.';
+comment on column public.exam_questions.preamble is
+  'Stem prose introducing this question/part, from the analyse contract v2 (structure.preamble_by_label): {n, text:[lines], nontext:[{kind,cls,page}], bands:[{page,y0,y1}]}. Authoritative text for the outline view; the band is also drawn as a context_type=preamble region.';

volumes/db/cc/79-class-markbook.sql

@@ -0,0 +1,115 @@
+-- 79-class-markbook.sql
+-- G2 running markbook / gradebook: teacher-editable per-class assessment columns and marks.
+
+create table if not exists public.class_assessments (
+    id uuid primary key default gen_random_uuid(),
+    class_id uuid not null references public.classes(id) on delete cascade,
+    tenant_id uuid not null references public.institutes(id) on delete cascade,
+    title text not null check (length(btrim(title)) > 0),
+    date date,
+    max_marks numeric not null default 100 check (max_marks > 0),
+    created_at timestamptz not null default now(),
+    updated_at timestamptz not null default now()
+);
+
+create table if not exists public.assessment_marks (
+    id uuid primary key default gen_random_uuid(),
+    assessment_id uuid not null references public.class_assessments(id) on delete cascade,
+    student_id uuid not null references public.profiles(id) on delete cascade,
+    tenant_id uuid not null references public.institutes(id) on delete cascade,
+    mark numeric check (mark is null or mark >= 0),
+    updated_by uuid references public.profiles(id),
+    updated_at timestamptz not null default now(),
+    constraint assessment_marks_assessment_student_key unique (assessment_id, student_id)
+);
+
+create index if not exists idx_class_assessments_class on public.class_assessments(class_id);
+create index if not exists idx_class_assessments_tenant on public.class_assessments(tenant_id);
+create index if not exists idx_assessment_marks_assessment on public.assessment_marks(assessment_id);
+create index if not exists idx_assessment_marks_student on public.assessment_marks(student_id);
+create index if not exists idx_assessment_marks_tenant on public.assessment_marks(tenant_id);
+
+create or replace function public.set_class_assessment_tenant()
+returns trigger language plpgsql security definer set search_path = public as $$
+begin
+  select c.institute_id into new.tenant_id from public.classes c where c.id = new.class_id;
+  if new.tenant_id is null then
+    raise exception 'class % not found for assessment tenant', new.class_id;
+  end if;
+  new.updated_at = now();
+  return new;
+end;
+$$;
+
+drop trigger if exists trg_set_class_assessment_tenant on public.class_assessments;
+create trigger trg_set_class_assessment_tenant
+before insert or update of class_id, title, date, max_marks on public.class_assessments
+for each row execute function public.set_class_assessment_tenant();
+
+create or replace function public.set_assessment_mark_tenant_and_validate()
+returns trigger language plpgsql security definer set search_path = public as $$
+declare
+  a record;
+begin
+  select ca.tenant_id, ca.max_marks, ca.class_id into a
+  from public.class_assessments ca
+  where ca.id = new.assessment_id;
+  if a.tenant_id is null then
+    raise exception 'assessment % not found for mark tenant', new.assessment_id;
+  end if;
+  if new.mark is not null and new.mark > a.max_marks then
+    raise exception 'mark % exceeds assessment max %', new.mark, a.max_marks;
+  end if;
+  if not exists (
+    select 1 from public.class_students cs
+    where cs.class_id = a.class_id and cs.student_id = new.student_id and cs.status = 'active'
+  ) then
+    raise exception 'student % is not active in assessment class', new.student_id;
+  end if;
+  new.tenant_id = a.tenant_id;
+  new.updated_at = now();
+  return new;
+end;
+$$;
+
+drop trigger if exists trg_set_assessment_mark_tenant_validate on public.assessment_marks;
+create trigger trg_set_assessment_mark_tenant_validate
+before insert or update of assessment_id, student_id, mark on public.assessment_marks
+for each row execute function public.set_assessment_mark_tenant_and_validate();
+
+alter table public.class_assessments enable row level security;
+alter table public.assessment_marks enable row level security;
+
+-- Assessment columns: class teachers/admins can read/write.
+drop policy if exists class_assessments_service_role on public.class_assessments;
+create policy class_assessments_service_role on public.class_assessments
+  using (auth.role() = 'service_role')
+  with check (auth.role() = 'service_role');
+
+drop policy if exists class_assessments_class_staff_rw on public.class_assessments;
+create policy class_assessments_class_staff_rw on public.class_assessments 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));
+
+-- Marks: staff can read/write; students can read their own rows.
+drop policy if exists assessment_marks_service_role on public.assessment_marks;
+create policy assessment_marks_service_role on public.assessment_marks
+  using (auth.role() = 'service_role')
+  with check (auth.role() = 'service_role');
+
+drop policy if exists assessment_marks_class_staff_rw on public.assessment_marks;
+create policy assessment_marks_class_staff_rw on public.assessment_marks for all to authenticated
+  using (exists (
+    select 1 from public.class_assessments ca
+    where ca.id = assessment_marks.assessment_id
+      and (public.is_class_teacher(ca.class_id) or public.is_class_admin(ca.class_id))
+  ))
+  with check (exists (
+    select 1 from public.class_assessments ca
+    where ca.id = assessment_marks.assessment_id
+      and (public.is_class_teacher(ca.class_id) or public.is_class_admin(ca.class_id))
+  ));
+
+drop policy if exists assessment_marks_student_read on public.assessment_marks;
+create policy assessment_marks_student_read on public.assessment_marks for select to authenticated
+  using (student_id = auth.uid());