Module · Exams & Marks

-- An exam event — e.g. "Half-Yearly 2026" for Grade 5
create table public.exams (
  id          uuid primary key default gen_random_uuid(),
  branch_id   uuid not null references public.branches(id),
  name        text not null,
  grade       text not null,
  start_date  date not null,
  end_date    date,
  result_date date,
  status      text not null default 'scheduled' check (status in ('scheduled', 'ongoing', 'results_pending', 'published')),
  created_at  timestamptz not null default now()
);
 
-- Each subject paper inside an exam
create table public.exam_subjects (
  id           uuid primary key default gen_random_uuid(),
  exam_id      uuid not null references public.exams(id) on delete cascade,
  subject      text not null,
  exam_date    date not null,
  max_marks    int not null check (max_marks > 0),
  passing_marks int not null default 35
);
 
-- One row per student per subject
create table public.exam_marks (
  id              uuid primary key default gen_random_uuid(),
  exam_subject_id uuid not null references public.exam_subjects(id) on delete cascade,
  student_id      uuid not null references public.students(id) on delete cascade,
  marks_obtained  numeric(5, 2),  -- nullable until entered
  absent          boolean not null default false,
  remarks         text,
  entered_by      uuid references public.profiles(id),
  entered_at      timestamptz,
 
  unique (exam_subject_id, student_id)
);
 
-- Enable RLS, add branch-or-super policies as standard
-- (parent visibility: only when exams.status = 'published')

const ExamSchema = z.object({
  name:       z.string().min(2),
  grade:      z.string().min(1),
  start_date: z.string(),
  end_date:   z.string().optional(),
  subjects:   z.array(z.object({
    subject:       z.string().min(2),
    exam_date:     z.string(),
    max_marks:     z.number().int().positive(),
    passing_marks: z.number().int().nonnegative(),
  })).min(1),
})

create or replace function public.create_exam(payload jsonb)
returns uuid
language plpgsql security invoker as $$
declare
  new_exam_id uuid;
  subj jsonb;
begin
  insert into public.exams (branch_id, name, grade, start_date, end_date)
  values (
    (payload->>'branch_id')::uuid,
    payload->>'name',
    payload->>'grade',
    (payload->>'start_date')::date,
    (payload->>'end_date')::date
  )
  returning id into new_exam_id;
 
  for subj in select * from jsonb_array_elements(payload->'subjects')
  loop
    insert into public.exam_subjects (exam_id, subject, exam_date, max_marks, passing_marks)
    values (
      new_exam_id,
      subj->>'subject',
      (subj->>'exam_date')::date,
      (subj->>'max_marks')::int,
      coalesce((subj->>'passing_marks')::int, 35)
    );
  end loop;
 
  return new_exam_id;
end;
$$;

const { data: examId, error } = await supabase.rpc('create_exam', { payload: formData })

Half-Yearly 2026 · Mathematics · Grade 5A
Max marks: 100, Passing: 35

────────────────────────────────────
001  Arjun Sharma     [  85 ]  □ Absent
002  Bhavya Reddy     [  72 ]  □ Absent
003  Chinmay Patel    [  34 ]  □ Absent  ← below passing (red highlight)
004  Diya Mehta       [     ]  ☑ Absent
...

[Save 26 entries]

create or replace view public.report_card_view as
select
  em.id as mark_id,
  em.student_id,
  s.full_name      as student_name,
  s.roll_no,
  e.id             as exam_id,
  e.name           as exam_name,
  e.status         as exam_status,
  es.subject,
  es.max_marks,
  es.passing_marks,
  em.marks_obtained,
  em.absent,
  case
    when em.absent then 'A'
    when em.marks_obtained >= es.passing_marks then 'P'
    else 'F'
  end as pass_status
from public.exam_marks em
join public.exam_subjects es on es.id = em.exam_subject_id
join public.exams e on e.id = es.exam_id
join public.students s on s.id = em.student_id;