Module · Circulars

create table public.circulars (
  id            uuid primary key default gen_random_uuid(),
  branch_id     uuid not null references public.branches(id),
  subject       text not null,
  body_md       text not null,                                          -- Markdown
  audience      text not null check (audience in ('all', 'grade', 'section', 'parent')),
  grade         text,                                                   -- For audience = 'grade'
  section_id    uuid references public.class_sections(id),              -- For audience = 'section'
  recipient_parent_id uuid references public.profiles(id),              -- For audience = 'parent'
  status        text not null default 'draft' check (status in ('draft', 'queued', 'sending', 'sent', 'failed')),
  sent_at       timestamptz,
  created_by    uuid not null references public.profiles(id),
  created_at    timestamptz not null default now()
);
 
create table public.circular_recipients (
  id          uuid primary key default gen_random_uuid(),
  circular_id uuid not null references public.circulars(id) on delete cascade,
  parent_id   uuid not null references public.profiles(id),
  delivery_status text not null default 'pending' check (delivery_status in ('pending', 'sent', 'opened', 'failed')),
  sent_at     timestamptz,
  opened_at   timestamptz,
  error_msg   text,
 
  unique (circular_id, parent_id)
);
 
-- RLS:
--   Admins (super, branch) can insert circulars in their branch
--   Parents can see only rows in circular_recipients where parent_id = auth.uid()
--   Joined query: parents see circulars they're a recipient of

┌──────────────────────────────────────────┐
│  Subject:  [School closed on 14 May    ] │
│                                          │
│  Audience:                                │
│    ( ) All parents in Andheri (482)      │
│    (•) Specific grade  [Grade 5 ▼] (98)  │
│    ( ) Specific section [5A ▼]      (26) │
│    ( ) Just one parent  [search...]      │
│                                          │
│  Message:                                 │
│  ┌────────────────────────────────────┐  │
│  │ Dear parents,                       │  │
│  │                                     │  │
│  │ Due to the local elections,         │  │
│  │ school will remain closed on        │  │
│  │ Thursday, 14 May 2026.              │  │
│  │ ...                                 │  │
│  └────────────────────────────────────┘  │
│                                          │
│  [Save draft]   [Preview]   [Send to 98] │
└──────────────────────────────────────────┘

create or replace function public.count_circular_recipients(
  p_branch_id uuid,
  p_audience text,
  p_grade text default null,
  p_section_id uuid default null,
  p_parent_id uuid default null
)
returns int
language sql
stable
security invoker
as $$
  select count(distinct s.parent_id)::int
  from public.students s
  where s.deleted_at is null
    and s.branch_id = p_branch_id
    and case
      when p_audience = 'all'     then true
      when p_audience = 'grade'   then s.section_id in (select id from public.class_sections where grade = p_grade)
      when p_audience = 'section' then s.section_id = p_section_id
      when p_audience = 'parent'  then s.parent_id = p_parent_id
    end;
$$;

// supabase/functions/send-circular/index.ts
import { createClient } from "@supabase/supabase-js@2.45.0";
import { Resend } from "npm:resend@4.0.0";
 
const resend = new Resend(Deno.env.get("RESEND_API_KEY")!);
 
Deno.serve(async (req) => {
  const { circular_id } = await req.json();
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
  );
 
  // Fetch circular and recipients
  const { data: circular } = await supabase
    .from("circulars")
    .select("*, branches(name)")
    .eq("id", circular_id)
    .single();
 
  if (!circular) return new Response("Not found", { status: 404 });
 
  const { data: recipients } = await supabase
    .from("circular_recipients")
    .select("id, parent_id, profiles!inner(email, full_name)")
    .eq("circular_id", circular_id)
    .eq("delivery_status", "pending");
 
  await supabase.from("circulars").update({ status: "sending" }).eq("id", circular_id);
 
  for (const r of recipients ?? []) {
    const profile = r.profiles as any;
    try {
      await resend.emails.send({
        from: `Aurora School <circulars@aurora-school.in>`,
        to: profile.email,
        subject: circular.subject,
        text: circular.body_md,  // Or render markdown to HTML
      });
      await supabase
        .from("circular_recipients")
        .update({ delivery_status: "sent", sent_at: new Date().toISOString() })
        .eq("id", r.id);
    } catch (err) {
      await supabase
        .from("circular_recipients")
        .update({ delivery_status: "failed", error_msg: String(err) })
        .eq("id", r.id);
    }
  }
 
  await supabase
    .from("circulars")
    .update({ status: "sent", sent_at: new Date().toISOString() })
    .eq("id", circular_id);
 
  return new Response("ok");
});

This year (12)

Mon 12 May  · School closed on 14 May               [open]
Fri 9 May   · Half-yearly exam schedule released     [open]
Mon 5 May   · Grade 5A annual day rehearsal — Tue    [open]
...

const { data } = await supabase
  .from('circular_recipients')
  .select('id, sent_at, circulars!inner(id, subject, body_md, sent_at)')
  .eq('parent_id', user.id)
  .order('sent_at', { ascending: false, foreignTable: 'circulars' })