chore: record supabase drift sync t_5cab50e2

2026-06-09 07:03:59 +01:00
4 changed files with 0 additions and 158 deletions
@@ -1,12 +0,0 @@
--==========================================================================================
-- 75. exam_response_areas.meta — rich recognition payload from the extraction service (P2)
--==========================================================================================
-- The extraction service (docling-exam-spike analyse contract) returns richer per-region detail than
-- the thin first-pass could: per-option OMR box geometry + fill (boxes[]), select_n ("tick 2 of 5"),
-- n_options, the expected unit/quantity for single-line answers, and the data_table an MC question reads.
-- These have no dedicated columns; keep them in a jsonb sidecar so the canvas can render tick boxes /
-- units / MC option counts without a column per attribute. Nullable, defaults to {}.
-alter table public.exam_response_areas add column if not exists meta jsonb not null default '{}'::jsonb;
-
-comment on column public.exam_response_areas.meta is
-  'Extraction-service recognition detail: {n_lines, unit, quantity, n_boxes, n_options, select_n, box_form, boxes:[{x0,y0,x1,y1,fill}], table:{grid,n_cells}}. Rendered by the canvas; not authoritative for marking.';
@@ -1,11 +0,0 @@
--==========================================================================================
-- 76. exam_templates.extraction_meta — trust signal + provenance from the extraction service (P3)
--==========================================================================================
-- Records, per template, how the recognition was produced and whether it reconciled: the audit
-- cover-total gate (cover_total vs detected_total, status, anomaly), the engine + slug used, and
-- region counts. Lets the setup UI show "extraction: cover 100 = detected 100 ✓" (or flag an under-read),
-- and lets the digital-text view resolve the paper's replica by slug. Nullable, defaults to {}.
-alter table public.exam_templates add column if not exists extraction_meta jsonb not null default '{}'::jsonb;
-
-comment on column public.exam_templates.extraction_meta is
-  'Extraction-service provenance + trust gate: {engine, slug, audit:{cover_total,detected_total,status,anomaly}, counts}.';
@@ -1,20 +0,0 @@
--==========================================================================================
-- 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.';
@@ -1,115 +0,0 @@
-- 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());