Module · Students

create table public.students (
  id              uuid primary key default gen_random_uuid(),
  admission_no    text not null,
  full_name       text not null,
  date_of_birth   date not null,
  gender          text check (gender in ('male', 'female', 'other')),
  parent_id       uuid not null references public.parents(id) on delete restrict,
  branch_id       uuid not null references public.branches(id) on delete restrict,
  section_id      uuid references public.class_sections(id),
  roll_no         text,
  photo_url       text,
  emergency_phone text,
  notes           text,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  deleted_at      timestamptz,
 
  unique (branch_id, admission_no),
  unique (section_id, roll_no)
);
 
alter table public.students enable row level security;
 
-- Branch isolation: super admin sees all, branch admin sees own branch,
-- teacher sees students in their assigned sections, parent sees own children.
create policy "students_select" on public.students
  for select using (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
    or (
      -- Parents: only their own children
      parent_id in (select id from public.parents where id = auth.uid())
    )
    or (
      -- Teachers: only students in their sections
      section_id in (
        select id from public.class_sections where teacher_id = auth.uid()
      )
    )
  );
 
create policy "students_insert" on public.students
  for insert with check (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
  );
 
create policy "students_update" on public.students
  for update using (
    public.is_super_admin()
    or branch_id = public.current_branch_id()
  );
 
create policy "students_delete" on public.students
  for delete using (
    public.is_super_admin()
  );

pnpm supabase gen types typescript --linked > src/lib/database.types.ts

import type { Database } from '@/lib/database.types'
 
export type Student   = Database['public']['Tables']['students']['Row']
export type StudentIn = Database['public']['Tables']['students']['Insert']

import { createServerClient } from '@/lib/supabase/server'
import { StudentsTable } from './StudentsTable'
 
export default async function StudentsPage() {
  const supabase = await createServerClient()
  const { data: students } = await supabase
    .from('students')
    .select('id, admission_no, full_name, roll_no, branch_id, section_id, photo_url')
    .is('deleted_at', null)
    .order('full_name')
 
  return (
    <main className="max-w-7xl mx-auto px-4 py-8">
      <header className="flex items-center justify-between mb-6">
        <div>
          <h1 className="text-2xl font-bold tracking-tight">Students</h1>
          <p className="text-sm text-gray-500">{students?.length ?? 0} active enrolments</p>
        </div>
        <a href="/admin/students/new" className="bg-indigo text-white px-4 py-2 rounded-lg font-semibold">+ Enrol student</a>
      </header>
      <StudentsTable initialData={students ?? []} />
    </main>
  )
}

'use client'
import { useQuery } from '@tanstack/react-query'
import { createBrowserClient } from '@/lib/supabase/client'
import { useState } from 'react'
 
interface Props { initialData: Student[] }
 
export function StudentsTable({ initialData }: Props) {
  const [search, setSearch] = useState('')
 
  const { data } = useQuery({
    queryKey: ['students', search],
    queryFn: async () => {
      const supabase = createBrowserClient()
      let q = supabase.from('students').select('*').is('deleted_at', null)
      if (search) q = q.ilike('full_name', `%${search}%`)
      const { data } = await q.order('full_name')
      return data ?? []
    },
    initialData,
    staleTime: 60_000,  // STALE_TIMES.MEDIUM
  })
 
  return (
    <div>
      <input
        placeholder="Search by name..."
        value={search}
        onChange={e => setSearch(e.target.value)}
        className="w-full max-w-sm px-3 py-2 border rounded-lg mb-4"
      />
      <table className="w-full">
        <thead className="text-xs text-gray-500 uppercase tracking-wider border-b">
          <tr>
            <th className="text-left py-2">Adm No</th>
            <th className="text-left py-2">Name</th>
            <th className="text-left py-2">Roll</th>
            <th></th>
          </tr>
        </thead>
        <tbody>
          {data.map(s => (
            <tr key={s.id} className="border-b hover:bg-gray-50">
              <td className="py-3 font-mono text-sm">{s.admission_no}</td>
              <td className="py-3 font-medium">{s.full_name}</td>
              <td className="py-3 text-sm text-gray-500">{s.roll_no ?? '—'}</td>
              <td className="py-3 text-right">
                <a href={`/admin/students/${s.id}`} className="text-indigo text-sm">Open →</a>
              </td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  )
}

const EnrolSchema = z.object({
  full_name:      z.string().min(2),
  date_of_birth:  z.string().refine(d => new Date(d) < new Date(), "Must be in the past"),
  gender:         z.enum(['male', 'female', 'other']),
  parent_id:      z.string().uuid("Pick a parent"),
  branch_id:      z.string().uuid(),
  section_id:     z.string().uuid().optional(),
  admission_no:   z.string().min(1),
  roll_no:        z.string().optional(),
  emergency_phone: z.string().regex(/^[6-9]\d{9}$/, "10 digits, starting 6-9").optional().or(z.literal('')),
})

const { error } = await supabase.from('students').insert({
  ...data,
  branch_id: data.branch_id ?? currentUserBranch,
})
if (error) return toast.error(humanizeError(error))
toast.success(`Enrolled ${data.full_name}`)
queryClient.invalidateQueries({ queryKey: ['students'] })
router.push('/admin/students')

const onDelete = async (id: string) => {
  if (!confirm('Soft-delete this student? They can be restored later.')) return
  const { error } = await supabase
    .from('students')
    .update({ deleted_at: new Date().toISOString() })
    .eq('id', id)
  if (error) return toast.error(humanizeError(error))
  toast.success('Student archived')
  queryClient.invalidateQueries({ queryKey: ['students'] })
}