Module · Attendance

create table public.attendance (
  id         uuid primary key default gen_random_uuid(),
  student_id uuid not null references public.students(id) on delete cascade,
  date       date not null,
  status     text not null check (status in ('present', 'absent', 'late', 'excused')),
  marked_by  uuid not null references public.profiles(id),
  branch_id  uuid not null references public.branches(id) on delete restrict,
  notes      text,
  created_at timestamptz not null default now(),
 
  unique (student_id, date)  -- One row per student per day
);
 
alter table public.attendance enable row level security;
 
-- Same branch-or-super rule, plus parent sees own child's attendance
create policy "attendance_select" on public.attendance
  for select using (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
    or student_id in (
      select id from public.students where parent_id = auth.uid()
    )
  );
 
-- Only super admin, branch admin, and assigned teachers can mark
create policy "attendance_insert" on public.attendance
  for insert with check (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
  );
 
create policy "attendance_update" on public.attendance
  for update using (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
  );

┌─────────────────────────────────────────┐
│  Grade 5A — 26 students                 │
│  Today: Mon 11 May                       │
│                                          │
│  [Mark All Present]  [Save 28 changes]   │
│                                          │
│  ───────────────────────────────────    │
│  001  Arjun Sharma         [P] A L E    │
│  002  Bhavya Reddy         P [A] L E    │
│  003  Chinmay Patel        [P] A L E    │
│  004  Diya Mehta           [P] A L E    │
│  ...                                     │
└─────────────────────────────────────────┘

'use client'
import { useQuery, useMutation, useQueryClient } from '@tanstack/react-query'
import { createBrowserClient } from '@/lib/supabase/client'
import { useState, useMemo } from 'react'
 
type Status = 'present' | 'absent' | 'late' | 'excused'
 
export default function MarkAttendancePage({ params }: { params: { id: string } }) {
  const sectionId = params.id
  const today = useMemo(() => new Date().toISOString().slice(0, 10), [])
  const supabase = createBrowserClient()
  const qc = useQueryClient()
 
  // Fetch students in this section
  const { data: students = [] } = useQuery({
    queryKey: ['section-students', sectionId],
    queryFn: async () => {
      const { data } = await supabase
        .from('students')
        .select('id, roll_no, full_name')
        .eq('section_id', sectionId)
        .is('deleted_at', null)
        .order('roll_no')
      return data ?? []
    },
  })
 
  // Fetch existing attendance for today (so teacher can edit, not re-mark)
  const { data: existing = [] } = useQuery({
    queryKey: ['attendance', sectionId, today],
    queryFn: async () => {
      const { data } = await supabase
        .from('attendance')
        .select('student_id, status')
        .eq('date', today)
        .in('student_id', students.map(s => s.id))
      return data ?? []
    },
    enabled: students.length > 0,
  })
 
  // Local state — keyed by student_id
  const [marks, setMarks] = useState<Record<string, Status>>(() => {
    const initial: Record<string, Status> = {}
    students.forEach(s => { initial[s.id] = 'present' })  // Default everyone to present
    existing.forEach(e => { initial[e.student_id] = e.status as Status })  // Override with saved
    return initial
  })
 
  const markAll = (status: Status) => {
    setMarks(prev => Object.fromEntries(Object.keys(prev).map(k => [k, status])))
  }
 
  const save = useMutation({
    mutationFn: async () => {
      const rows = students.map(s => ({
        student_id: s.id,
        date: today,
        status: marks[s.id] ?? 'present',
        // marked_by + branch_id will be filled from auth context server-side via a trigger
      }))
      const { error } = await supabase.from('attendance').upsert(rows, {
        onConflict: 'student_id,date',
      })
      if (error) throw error
    },
    onSuccess: () => {
      qc.invalidateQueries({ queryKey: ['attendance', sectionId, today] })
      toast.success(`Saved ${students.length} students`)
    },
  })
 
  return (
    <main className="max-w-2xl mx-auto px-4 py-6">
      {/* render header + buttons + rows */}
    </main>
  )
}

create or replace function public.fill_attendance_meta()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  new.marked_by := auth.uid();
  new.branch_id := (
    select s.branch_id from public.students s where s.id = new.student_id
  );
  return new;
end;
$$;
 
create trigger attendance_meta_trigger
before insert or update on public.attendance
for each row execute function public.fill_attendance_meta();

May 2026
M  T  W  T  F  S  S
                1  2  3
4  5  6  7  8  9  10
.  .  .  .  .  .  .

create or replace function public.attendance_summary_by_section(
  start_date date,
  end_date date
)
returns table (
  section_id uuid,
  section_name text,
  total_marks bigint,
  present_count bigint,
  attendance_pct numeric
)
language sql
stable
security invoker
as $$
  select
    cs.id,
    'Grade ' || cs.grade || cs.section,
    count(a.id),
    count(a.id) filter (where a.status = 'present'),
    round(100.0 * count(a.id) filter (where a.status = 'present') / nullif(count(a.id), 0), 1)
  from public.class_sections cs
  left join public.students s on s.section_id = cs.id
  left join public.attendance a on a.student_id = s.id and a.date between start_date and end_date
  where cs.deleted_at is null
  group by cs.id, cs.grade, cs.section
$$;

const { data } = await supabase.rpc('attendance_summary_by_section', {
  start_date: '2026-04-01',
  end_date: '2026-05-31',
})