Communication hub.

Most platforms treat the layer underneath this sentence as a feed widget — a stream of cards in some corner of the page, decorative, optional, easy to skip. We disagree. A school is a community: students who run clubs, parents who book conferences, peers who recognise each other, governments that take petitions seriously, conversations that thread for weeks. The communication cluster is what carries that life.

The thirteen chapters that follow open the cluster in the order a school encounters it. Day 0 is settings (Chapter One): who can chat with whom, what gets moderated, what languages travel, how channels fan. Day 1 is messages (Chapter Two) and the principal's first announcement (Chapter Three). The notifications inbox (Chapter Four) is where every signal collects. The social life — feed, stories, forums — is Chapters Five through Seven. Calendar events and parent-teacher conferences are Eight and Nine. Student government, clubs, profile + reputation, and the admin's template library round out Ten through Thirteen.

One book, thirteen chapters, kept side by side. Read this one first.

Before any teacher sends a message, before any parent receives a push, before any student posts on the feed — the school admin opens one page and tells YESS the discourse rules. The page lives at /dashboard/communication/settings. It carries twelve school-level settings spread across four sections, plus a sticky-footer "Save all changes" button with an isDirty pulse so partial saves are never ambiguous.

The settings are not decorative. Each one is consumed by a specific downstream surface — the chat composer, the parent-teacher request flow, the moderation queue, the social-feed insert trigger, the read-receipt UI, the dispatcher's Bergamot translation pipeline, the template-locale resolution chain, and the auto-room provisioner. Day 0 is when the school's voice is calibrated; everything from Day 1 onward respects what Day 0 chose.

Section 1 · Discourse rules

Six settings sit in this section. Each one answers a real question a school asks on its first day.

• Allow student chat. Default off. When off, students can read class and subject channels but cannot send messages — teachers and staff are not affected. Toggle on the day you trust the year group with voice.
• Allow file attachments. Default on. Turns the paperclip in the composer on or off school-wide. Disabling this disables the per-message attachment cap below — there's no "files up to this size" when the school has banned files entirely.
• Max attachment size (MB). Default 10. Hard cap per attachment. Suggested 10-50 MB depending on the school's storage budget; this tightens automatically when "Allow file attachments" is off.
• Moderate messages before send. Default off. Every chat message lands in a moderation queue before peers see it. Slows conversation; only enable when reputation risk is high or the platform is under audit.
• Moderate student social-feed posts. Default on. When a student without social_feed.moderate posts on /portal/social, the BEFORE-INSERT trigger fn_social_feed_posts_set_status (migration 00388) lands the row status='pending'. A moderator (staff with the permission OR a student-government position holder with can_moderate_content=true) approves from /dashboard/social-feed's moderation tab. Schools with a healthy peer-mod culture can turn this off.
• Read receipts (school override). Default on. When off, the entire school opts out of ✓✓ blue read indicators on outgoing messages — even if individual user preferences would enable them. The school override wins.

Section 2 · Parent–teacher channels

Adult-to-adult conversations about a minor are the highest- stakes surface in the cluster. Two settings calibrate the friction.

• Policy. Three choices: staff_only (default — only school-authorised staff can initiate; parents wait to be invited), school_authorized (teachers can initiate, but each new parent-teacher pair lands in the admin's queue at /dashboard/messages's Parent-Teacher tab for approval before becoming active), open (teachers and parents both initiate freely, no admin gate).
• Parents can initiate the request. Default off. When off, the portal's "Start a conversation" affordance is hidden from parent accounts; they wait for a teacher to reach out. Independent of the three policies above, but only meaningful when policy is school_authorized or open.

Section 3 · Translation & locale

Two settings decide how the dispatcher chooses the language for each recipient. Africa-and-Middle-East schools rarely run one-language only.

• Disable on-device translation. Default off. When on, announcements and notifications skip the Bergamot translation pipeline — every recipient gets the source language, regardless of their personal preference. Schools serving a single-language community turn this on to save the translation cost.
• Locale fallback order. Default ['en']. When a recipient's preferred language has no template translation, the dispatcher walks this list to find one. The fallback chain is a chip list — admin adds a locale via the dropdown, removes by clicking the × on a chip. The order of the chips IS the fallback order (first chip = first fallback). Wins live in the render_message_template RPC at send time.

Section 4 · Automation

Chat rooms that should appear without anyone having to click "New."

• Auto-create class group chats. Default on. Every class section gets a chat room (chat_rooms.room_type='class_group') when the class is created. Teacher + students are auto-added as members. Disable only if your school prefers single-subject chats over class-wide.
• Auto-create subject group chats. Default off. Every subject offering gets its own chat room. Useful when subjects span sections; noisy if the school is small. The two automation toggles are independent — you can enable one, both, or neither.

Where each setting travels

The twelve settings are written once and read by eight downstream surfaces. Each row below was verified against the live code under the 4-gate discipline (page read 3×, hook read 3×, migration read 3×).

• allow_student_chat + allow_file_sharing + max_file_size_mb → the chat composer's canSend gate + paperclip visibility + per- attachment cap at /dashboard/messages and /portal/messages.
• parent_teacher_policy + parent_can_initiate → useAuthorizeParentTeacherConversation + the portal's "Start a conversation" affordance visibility.
• require_message_moderation → chat messages route through the moderation queue when the sender's role requires it.
• moderate_student_posts → the social-feed BEFORE-INSERT trigger fn_social_feed_posts_set_status (migration 00388) decides between 'pending' and 'approved'.
• read_receipts_school_override → the MessageList primitive renders ✓✓ only when this setting AND the recipient's personal pref both allow.
• bergamot_disabled → the dispatcher Edge Function skips on-device translation when this is true.
• template_locale_fallback_order → render_message_template(template_id, context) RPC walks the chain at send time.
• auto_create_class_groups + auto_create_subject_groups → class / subject creation triggers auto-provision a chat_rooms row with the right room_type.

The most-used surface in YESS. A teacher messages a parent about a quiz score. A vice-principal cascades a Friday update to every classroom group. A student asks her form tutor for a clarification on tomorrow's homework. A bus monitor confirms drop-off with a single emoji reaction. Every conversation obeys the policy from Chapter One: who can send, what can be attached, whether read receipts fire, which channels deliver after-hours.

Thirteen kinds of room

The chat_rooms.room_type CHECK constraint — extended by migration 00390 — accepts thirteen values. Nine of them are creatable from the New Room dialog in /dashboard/messages; four more are provisioned by other cluster workflows (student government + parent-teacher conferences) and the dialog doesn't expose them by design.

• direct — exactly two members. The classic 1:1.
• class_group — teacher + every enrolled student. Auto-provisioned when auto_create_class_groups (Chapter 1) is on.
• subject_group — teacher + students in a subject section. Auto-provisioned via auto_create_subject_groups (default off).
• department_group — staff-only by department. The maths office room.
• campus_staff — all staff at one campus. "Fire drill at 11am" broadcast room.
• year_group — every student in a year of study. Year-wide notes that don't warrant a school announcement.
• staff_room — virtual lounge. All staff at the school regardless of campus.
• parent_teacher — gated by Chapter 1's parent_teacher_policy. The chat row is paired with a parent_teacher_conversations authorisation row (status: pending / approved / rejected / closed). Composer reads the status before allowing a send.
• custom — ad-hoc rooms for committees, exam invigilators, event volunteers. Anyone with communication_chat.create.
• town_hall — created by Chapter 10's student government workflow when a town hall is scheduled. Hosts the live Q&A room.
• office_hours — recurring per-position slot room; also a Chapter 10 surface.
• advisor_channel — counsellor-led advisory groups, scoped per cohort.
• mentorship_pair — alumni-student mentorship 1:1 (Chapter 12's profile + reputation cluster uses this when a pairing is approved).

The composer primitives

Both dashboard ( /dashboard/messages) and portal ( /portal/messages) mount the same components/messaging/Composer primitive. Eight affordances on the rail:

• Paperclip — attaches a file via Cloudinary; hidden when allow_file_sharing=false. Per-attachment cap from max_file_size_mb.
• Emoji popover — 24 quick-pick emojis from EMOJI_QUICK; inserts at cursor position.
• Templates dropdown (FileText icon) — only rendered when templates.length > 0. Populated from useMessageTemplates (Chapter 13). Picking inserts the body raw with {{tokens}} intact.
• Scheduled-send (calendar-clock icon) — only rendered when onSchedule prop is wired. Opens a datetime-local input; on confirm calls useScheduleMessage which inserts the row with scheduled_status='queued' for the cron (Chapter 13) to promote.
• Reply chip — when replyTo is set, renders the quoted bubble above the textarea with a cancel X. The insert carries reply_to_id for thread rendering.
• Edit chip — same shape as reply chip, different colour. Submits via useEditMessage. Edited messages render "(edited)" inline with the original edited_at timestamp.
• Character counter — warns at 900 of a 1000 cap; blocks send above 1000.
• Send — Enter sends; Shift+Enter inserts newline; Cmd/Ctrl+Enter also sends.

A typical Tuesday

• 9:14am. Mrs Tanoh, Form 4 maths teacher, opens /dashboard/messages and taps her 1:1 with Mr Mensah, a Form-4 parent. Composer reads communication_settings.allow_file_sharing + max_file_size_mb — paperclip on, cap 10 MB. She attaches the mock-exam PDF + types "for context on our chat earlier" + hits Enter. The row writes to messages; an attachment row writes to message_attachments; the AFTER-INSERT trigger updates chat_rooms.last_message_at.
• 9:14am + 200ms. Dispatcher reads Mr Mensah's notification preferences (Chapter 4), sees push + WhatsApp enabled outside quiet hours. Push fires through OneSignal; WhatsApp fires through Twilio.
• 9:16am. Mr Mensah opens /portal/messages from the parent portal. Mrs Tanoh starts composing a follow-up; the typing indicator shows on his side from a row on typing_indicators via Supabase Realtime.
• 9:17am. He taps ❤ on her message. Row writes to message_reactions; Mrs Tanoh's bubble shows the reaction count update via Realtime within a second.
• 9:18am. The ✓✓ flips on her side as Mr Mensah's client writes chat_room_members.last_read_at. The column is on the supabase_realtime publication (migration 00370). The indicator only shows because BOTH the school's read_receipts_school_override (Chapter 1) AND Mensah's personal preference (Chapter 4) allow it.
• 11:50am. Mrs Tanoh needs to send the same message to all 30 Form-4 parents. She opens any 1:1 with one of them, taps the FileText icon, picks the school-pinned "Mock exam release" template (Chapter 13). Body inserts with {{exam_name}} + {{release_date}} placeholders. She fills values + hits send. She repeats the flow per parent — or schedules it for tonight.
• 3:42pm. Form 4's class group chat. A student asks "Sir is page 47 due tomorrow?" Form teacher is in a staff meeting. The student edits her message at 3:43pm to add "or page 48 too?" — the edit writes is_edited=true + edited_at; the bubble shows "(edited)" inline.
• 5:20pm. Form teacher replies. Composer calls useSendMessage with reply_to_id set; the bubble shows the threaded quote inline.
• 8:30pm. Vice-principal drafts "Reminder: parent-teacher meetings Saturday 9am-2pm." Calendar-clock icon → picks Friday 07:00 → confirm. useScheduleMessage inserts the row with scheduled_for='Friday 07:00' + scheduled_status='queued'. The every-minute cron yess_scheduled_message_promote (00399) flips it to 'sent' at 07:00 Friday; dispatcher fans it out.

What rides on realtime

Five tables published on supabase_realtime drive the chat surface's live behaviour:

• messages — new messages appear in the room without refresh.
• typing_indicators — ephemeral rows that auto-expire; "..." with pulse appears when a peer is composing.
• user_presence — online / away / offline / do-not-disturb per user; the green dot on the avatar is one subscription away.
• chat_room_members (00370) — when any peer flips last_read_at, the sender's outgoing bubble re-renders ✓→✓✓ live.
• parent_teacher_messages — the authorisation-gated parent-teacher conversation surface mirrors the same live behaviour.

A message that needs to reach more than two people, with the structure of a memo: title, body, priority, optional attachments, optional schedule, optional expiry. The principal's loudspeaker, the head of department's email-replacement, the form teacher's one-to-one note to a parent (an "individual" announcement is just an email with read tracking — schools use it for the parent who needs to see "your child was absent" with proof of delivery).

Three tables, one announcement

• announcements — the message itself: title, content, priority (normal / important / urgent), author_id, scheduled_at + is_published, expires_at, attachment_urls.
• announcement_targets — the audience rows. One per scope. Seven scope types after 00206.
• announcement_reads — per-recipient, per-announcement, timestamped. UNIQUE(announcement_id, user_profile_id) prevents double-counts. RLS was patched at 2026-05-19 (migrations 00382 + 00383) after auth.uid() vs user_profile_id comparisons silently blocked every legitimate read; the corrected RLS now uses get_user_profile_id().

Seven audience scopes

The author picks one scope per target row; an announcement can have multiple target rows. Each scope type's resolver joins to a different profile set at publish time:

• all — every active user_profiles row in the school.
• campus — every active user at the specified campus_id.
• class — every student in the class + their parents + the class teacher.
• section — section's students + parents (tighter than class for split sections).
• role — every active user with the specified role_id ("all teachers", "all parents").
• department — every staff member whose department_id matches. Added in 00206; wired into the UI by commit cf8d0d87 (the schema had it for ~8 months before the dropdown exposed it). Heads of department broadcast to their office without role-spam.
• individual — one specific user_profile_id. The "email with read tracking" use case.

Dispatcher dedupes per recipient — a teacher who is also a Form-5 parent receives one delivery, not two, even when multiple target rows would otherwise fan to her.

A Friday afternoon

• 2:47pm. Principal opens /dashboard/announcements and clicks "New announcement". Title: "End-of-term assembly Saturday 9am". Body: "Doors open 8:30am; assembly hall; students in full uniform." Priority: important. Audience picker → Role → parent_guardian. Attaches the agenda PDF.
• 2:49pm. She wants the message to land at 5:00pm (parents picking up kids). Fills scheduled_at='17:00 today' and saves without flipping the publish switch. Row writes with is_published=false. It lands in the dashboard's "Scheduled" tab.
• 5:00pm. The every-minute cron yess_announcement_publish_due (migration 00537, shipped in commit e7923181) ticks. publish_due_announcements() finds her row, flips is_published=true, sets published_at=NOW(). The 00653 AFTER UPDATE trigger (shipped today by a parallel session) runs the audience expansion + writes one notifications row per target — silent off-portal fan-out is now fully closed across all five target types.
• 5:00pm + 1s. resolve_user_notification_channels resolves each parent's preferred channels (Chapter 4). 290 prefer WhatsApp, 110 push, 8 SMS, 4 email-only. External channels fire in parallel.
• 5:00pm + 4min. First reads arrive. A parent taps the push, opens /portal/announcements, sees the announcement. useAcknowledgeAnnouncement writes an announcement_reads row with read_at=now(). The dashboard's "612/800 read" counter (a COUNT over targets vs reads) ticks up live via useAnnouncementsRealtime.
• 7:15pm. Principal checks: 387/412 read. She drills into the "Read receipts" sheet, sees the 25 unread names, calls four personally — the ones whose kids regularly miss morning assembly. The phone calls happen because the tracker made the gap visible.

Every signal in YESS — attendance push, gradebook publish, forum reply, badge award, conference booked, fee receipt, @mention, town-hall question answered — passes through one place: the notifications fabric. Thirteen categories, five channels (in-app / push / email / SMS / WhatsApp), quiet hours per user, digest cadence per user, locale per user, WhatsApp consent per user. The dispatcher Edge Function is the only thing that touches OneSignal, Resend, Twilio, or the WhatsApp Business API. Every other module just inserts a row and trusts the dispatcher to fan it out.

The thirteen categories

A parent doesn't want the same volume of pings for "fee receipt" as for "child marked absent." YESS splits every signal into one of thirteen user-facing categories so the user picks which kind earns a push and which kind only writes to the in-app inbox. The list lives in the NOTIFICATION_CATEGORIES constant in use-my-notification-preferences.ts:

• academic — grade publish, assignment due, quiz available.
• financial — invoice ready, payment received, fee overdue.
• communication — chat message, school announcement, conference booked.
• social — @mention, comment on your post, forum reply, best answer accepted.
• attendance — absence flagged, late arrival, excuse rejected.
• transport — bus arriving, bus delayed, route change.
• admissions — applicant accepted, status change, welcome pack ready.
• lms — course content published, deadline reminder.
• incident — safeguarding report, urgent health update.
• discipline — conduct entry, restorative session scheduled.
• health — infirmary visit, vaccination reminder.
• newsletter — weekly digest, term-end summary.
• system — magic-link login, welcome, plan-change, password reset.

Engineer note: the DB CHECK on notifications.notification_type (migration 00280) accepts 16 values. The 13 above are user-toggle-able; three additional values — magic_link, welcome, plan_change — are auth/lifecycle signals the user can't opt out of, so they don't surface in the preferences grid and are rolled into "system" for the user-facing display.

The five channels

Each category × each channel is a separate toggle. 13 × 5 = 65 cells on the preferences grid (each category card has 5 channel switches when its master toggle is on).

• in_app — appears in the bell-icon inbox at /portal/notifications. Always on for every user (otherwise the inbox would be empty and nobody could deep-link from a push back into the platform).
• push — phone notification via OneSignal. Default-on for academic, financial, attendance, incident, health. The parent flips per category.
• email — Resend. Default-off for engagement signals (would flood the inbox), default-on for academic + financial + admissions (paper trail categories).
• sms — Africastalking (sub-Saharan providers) or Twilio. Default-off; opt-in by school because SMS budgets are real. Reserved for urgent attendance + incident.
• whatsapp — Twilio + Meta Cloud API. Gated by user_notification_settings.whatsapp_consent_at — until the user explicitly consents on the preferences page with their phone number, the dispatcher never sends WhatsApp to them (Meta Business policy). Revoke wipes the consent timestamp.

Delivery preferences

A separate card above the 13-category grid carries the per-user delivery policy on user_notification_settings (migration 00281):

• Quiet hours — start + end time (HH:MM). The dispatcher's process_notification_queue defers push + SMS + WhatsApp during the window; in-app + email always fire (the bell badge never silently grows in the night without the user knowing where to look). Critical priority bypasses the deferral.
• Digest mode — off / daily / weekly. Non-critical notifications roll into a single summary email at the user's chosen cadence.
• Preferred language — en / fr / ar. Drives the dispatcher's Bergamot translation pipeline for the message body. Inherits from the school's default when null.
• Read receipts — flip on/off whether your reading of someone else's message sends them a ✓✓. Honoured only when the school's read_receipts_school_override (Chapter One) is also on.
• WhatsApp consent — capture phone number, save with timestamp. Revoke button wipes both. Once consented, the dispatcher will fan the categories you've enabled WhatsApp on.

One parent, one Tuesday

Mr Ousmane is a Form-3 parent at Greenwood. Four notifications across the day, each routed by the rules above.

• 08:02am — absence. His son didn't make first period. Attendance trigger writes a row to notifications with category 'attendance'. Dispatcher reads Mr Ousmane's preferences for attendance: in_app=true, push=true, sms=true, whatsapp=true. 08:02 is inside his active window (quiet hours 22:00-07:00 not in play). All four channels fan in parallel. WhatsApp lands first; push second; SMS third; the in-app bell badge ticks immediately via Realtime.
• 11:30am — chat. Maths teacher messages him about his son's quiz. Category 'communication'. His prefs: in_app=true, push=true, whatsapp=true, sms=false, email=false. Three channels fire. He's in a meeting; ignores the WhatsApp buzz. The in-app inbox keeps it.
• 3:15pm — social. A teacher praised his son in the school feed. Category 'social' (re-categorised from 'communication' by migration 00485 so engagement signals are mute-able separately from admin signals). His prefs for social: in-app only. He sees it next time he opens the portal. No push, no WhatsApp.
• 10:35pm — invoice. Finance batches October statements. Category 'financial'. 10:35pm is inside Mr Ousmane's quiet window. The dispatcher writes the in-app row immediately and the email goes through (those don't respect quiet hours). Push + WhatsApp are deferred to 07:00 the next morning; his phone wakes him gently.

What the dispatcher actually reads

The chain matters because each layer answers a different question:

• resolve_user_notification_channels(profile_id, category) (00234 + extended in 00374) reads notification_preferences and returns the enabled channel array (email / sms / whatsapp) for that user × category. in_app is always-on; push fans out from the in-app row directly via the OneSignal adapter. This RPC does NOT enforce quiet hours / digest — a common misconception. The RPC answers "which channels did the user opt into."
• process_notification_queue (migration 00280, the dispatcher RPC itself) reads user_notification_settings.quiet_hours_* and digest_mode. If a target falls in quiet hours, push / SMS / WhatsApp rows are stamped dispatch_status='quiet_hours_deferred' and re-attempted at the next-allowed window. If digest_mode is daily/weekly, the rows queue and a future cron consolidates them. Critical priority bypasses both.
• The Edge Function at packages/db/supabase/functions/dispatch-notifications/ picks up pending rows, calls the resolved channels' external providers (OneSignal / Resend / Twilio / Meta Cloud API), records send results, retries on transient failures.

One canonical surface. Students post, position holders moderate, peers react, comment, mention each other, hashtag, bookmark, vote on polls. The dual-table fork that left pending posts orphaned was collapsed in May 2026; today every signal — from a teacher's "well done class" to a student's milestone celebration — lives on social_feed_posts with a clear status lifecycle (draft → pending → approved → rejected → archived) and a position-aware moderation gate driven by Chapter 1's moderate_student_posts toggle.

Seven kinds of post

The social_feed_posts.post_type CHECK constraint (migration 00389) accepts seven values:

• text — a simple status, the default.
• photo — one or more images via Cloudinary; multi-image posts use the media_urls array so a single post carries a full album from one row. (No separate "photo_album" type — older docs claimed one; the CHECK only accepts photo.)
• video — single uploaded video clip.
• poll — 2-10 options, single or multi-pick, optional close-at. Inserts into social_post_polls + a row per option.
• story — 24h ephemeral; covered in Chapter 6 (separate table; lives in the strip above the feed).
• milestone — celebration card (top mark, sports trophy, alumni achievement). Pairs with Chapter 12's community badges.
• event_recap — post-event photo album + attendance numbers + thank-you copy.

Eight ways to react

The original thumbs-up was replaced by the May-2026 engagement-depth migration with eight typed reactions: like · love · celebrate · support · insightful · funny · thoughtful · applause. Each is a row on social_feed_reactions with a CHECK constraint on type. The denorm social_feed_posts.likes_count (trigger- maintained since the Wave 36 denorm 00470) is the total across all types. The original bug — the client filtering to reaction_type='like' before counting — was caught in Wave 39 and fixed; today the count reads the denorm column directly.

How a post becomes visible

A student opens the composer and hits "Post." The BEFORE INSERT trigger fn_social_feed_posts_set_status (migration 00388) reads two things: the school's communication_settings.moderate_student_posts (Chapter 1, default true), and whether the caller has either the social_feed.moderate permission OR an active student-government position with can_moderate_content=true (Chapter 10). If moderation is on AND the caller can't moderate, the row lands status='pending'. Otherwise it goes straight to 'approved'.

The dashboard moderation tab at /dashboard/social-feed picks up pending posts. A moderator (staff with permission OR position holder via student_can_moderate()) taps approve or reject; the approve_social_post(p_id, p_decision, p_reason) RPC writes the decision, the audit log, and a notification to the author. Approved posts appear on every peer's feed via the supabase_realtime publication (00484). Rejected posts leave a reason on the author's draft so they know what to fix.

Teachers and staff bypass moderation by default — their posts go straight to 'approved'. Schools can flip moderate_student_posts off any time (Chapter 1 settings) without re-architecting.

Engagement signals

Comments live on social_feed_comments; mentions on social_post_mentions; bookmarks (self-only) on social_post_bookmarks; hashtags on social_post_hashtags with a per- school hashtags.usage_count denorm that drives the trending widget. Each engagement signal fires its own notification trigger:

• @mentions — Wave 33's 00467 trigger fires "X mentioned you" to the mentioned profile.
• Comments — Wave 50's 00482 trigger fires "X commented on your post" to the author (+ parent comment author when replying).
• Approval — approve_social_post writes an audit_logs row + dispatches a notification to the author with the decision.

Notifications for mention + comment land in the social category bucket (00485 split engagement signals out of the original communication bucket so parents can mute social chatter separately from admin signals — see Chapter 4).

Five reader surfaces

• /portal/social — school-wide feed; filters by all / pinned / mine / following (the "from people I follow" filter from Chapter 12).
• /portal/social/posts/<id> — single-post permalink with the full comment thread.
• /portal/social/mentions — durable archive of every post that's tagged you.
• /portal/social/bookmarks — posts you saved for later, self-only.
• /portal/social/hashtag/<tag> — per-tag feed.

Plus the moderation surface at /dashboard/social-feed for admins with tabs for All / Flagged / Pending.

A Form-5 student finishes painting the cafeteria mural at lunch. She wants every Form-5 to see it — but she doesn't want a photo pinned on her feed for the rest of the term. Stories are the half-life answer: a visual post that lives in a horizontal scroll-snap rail above the social feed for twenty-four hours, then quietly disappears from peers' eyes. The author and moderators can still see the row by permalink for a seven-day grace; after that the daily cron purges it from the database.

A story's lifetime

• 12:18pm Wednesday. Cafeteria mural done. She opens /portal/social and the StoryStrip sits above the feed, headlined "Stories" with a navy underline. Her own avatar isn't there yet; the first tile is the gold dashed "Add" ring.
• 12:19pm. She taps Add. The StoryUploader dialog opens — Cloudinary upload first, then preview + caption (200-char Textarea) + Post Story button. She uploads the mural photo, types "Painted today!", taps Post. useCreateStory.mutateAsync({ media_url, caption }) inserts a row in social_stories with expires_at = NOW() + INTERVAL '24 hours' (the hook clamps 1-48h; default 24). Toast: "Story posted." The strip re-fetches (60s refetch interval + an immediate invalidation on success) and her avatar appears at the front of the rail with the gradient ring — gold → rose → purple — and her first name below.
• 12:22pm. Peers tap her ring. The StoryViewer modal opens fullscreen against a black background: 9:16 aspect, 80vh max, photo top-bottom- covered. An 8-second white progress bar animates at the top; her avatar + name + a permalink "↗" + close-X sit in a row below the bar; her caption is rendered against a bottom gradient overlay. Tap close — no reactions, no comments, no poll. A story is a moment, not a debate.
• 2:42pm. A peer wants to share the permalink with a friend in chat. The viewer's ↗ button points at /portal/social/stories/<id>. The permalink page renders a YessPage shell + KenteRule navy + italic-gold "Story permalink" + the same 9:16 photo + a 2×1 dl showing Posted / Expires timestamps. If the viewer is the author, an italic note adds: "You authored this story. You can see it after expiry; peers cannot."
• 12:18pm Thursday. expires_at passes. Peer queries to useLiveStories filter expires_at > NOW() server-side, so her row vanishes from peer strips immediately. The RLS policy stories_select_live (00389) keeps the row visible to the author + moderators via an OR-clause; peers querying or visiting the permalink see an "Expired" badge + empty state.
• 04:33 UTC, +7 days. The cron yess_social_story_purge (migration 00466, '33 4 * * *') calls purge_expired_stories() which deletes every row where expires_at < NOW() - INTERVAL '7 days'. The permalink now returns the "Story not found or expired" panel for everyone, including the author. The Cloudinary asset stays until the storage provider's lifecycle rule sweeps it — that's a provider-side cron, not YESS's.

What a story isn't

The deliberate minimalism is the point. Compared to a social-feed post (Chapter Five), a story has no comment thread, no reaction picker, no poll, no @-mention surface, no #hashtag indexing, no bookmark, no draft state. The schema reflects that: social_stories is a single table — id, school_id, author_id, media_url, caption (nullable), expires_at, created_at, FK-joined author. No sibling tables. No moderation queue. Compare with social_feed_posts which has nine sibling tables (reactions, comments, mentions, hashtags, polls, poll-votes, bookmarks, post-hashtags, content-moderation). A story is the cluster's quietest surface; everything else carries more weight.

Where the story lives

• In the strip above /portal/social — the horizontal scroll-snap rail with author rings and the gold dashed "+" tile for self-creation. Refetches every 60s; new rows surface within a minute.
• In the StoryViewer modal — fullscreen tap-to-view with the 8-second progress bar; caption overlay; permalink + close X. Author / Owner of the modal lives at components/social/story-strip.tsx; the mobile equivalent at apps/mobile/app/(screens)/social-feed/index.tsx's StoryViewer component (read-only, no authoring).
• At the permalink /portal/social/stories/<id> — survivable URL for sharing in chat / notifications; also the only surface where the author can revisit the row after the 24h window closes.

Some conversations don't fit in chat and don't deserve to live forever on the feed. A student asks "Anyone else struggling with the new uniform rules?" — an anonymous thread is the right shape. A teacher posts "How are you handling the new IB calculator?" — a subject-forum thread collects responses for a week. A club president opens "Election manifesto draft, feedback welcome" — a club- forum thread with the best-answer flag. Forums are YESS's long-form conversation surface.

Four kinds of forum

• school — visible to every active user in the school. The "general" forum.
• class — visible to the class teacher + class students (optionally parents). Tied to a class_id.
• subject — every teacher + student in a subject section. Tied to subject_id.
• club — club members only. Tied to club_id.

Each forum holds threads (discussion_threads); each thread holds replies (discussion_replies); each thread can have one accepted "best-answer" reply via best_answer_reply_id. Subscriptions live on discussion_thread_subscriptions. Three denormalised counters maintained by triggers: discussion_forums.thread_count (Wave 41's 00474), discussion_threads.reply_count (full lifecycle Wave 37's 00471), and discussion_threads.subscriber_count (00392 base).

Anonymous threads — forums only

The social feed (Chapter 5) is intentionally not anonymous — student posts are signed, public, and bear consequence. Forums get the opposite affordance: a thread can be marked is_anonymous=true at creation. Peers see "Anonymous" in place of the author's name + avatar; the actual author_id stays on the row, visible to moderators only via a privileged SELECT path. The audit log captures both the action and the unmasked author for every moderated thread.

Anonymous posting is forum-only by design — never social feed. Anonymous reach + algorithmic amplification is the combination that breeds bullying. Forums are slower, scoped, and moderated; they earn the affordance.

The best-answer loop

A student opens "Quick Q on the trig homework, page 47 #5". Eight replies come in. One peer's answer is the one that actually unsticks her. She taps "Accept as best answer" on that reply. Three things happen in lockstep:

• The thread updates — best_answer_reply_id set on discussion_threads.
• The replier gets a notification — the 00396 trigger fires; her phone buzzes "Your reply was marked the best answer."
• First best-answer ever — the 00397 trigger fires the auto-award of the "Helpful" bronze badge. A separate notification arrives. The reputation cron at 04:09 UTC (Chapter 12) adds 15 (best_answers weight) + 10 (badges weight) = 25 to her score on the next nightly recompute.

UNIQUE(profile_id, badge_id) on profile_badges ensures the helpful badge is awarded exactly once per student no matter how many best-answers they accumulate.

Subscribe to a thread that lives

A student creates a thread. Wave 30's 00398 trigger auto- subscribes the author so she never misses a reply. Peers can subscribe via the bell affordance on the thread head; the SubscribeButton writes a row to discussion_thread_subscriptions and the subscriber_count denorm increments.

Every new reply on a subscribed thread fires the 00398 subscriber-notify trigger: one notification per subscriber, deep-linked to the reply. Subscriptions are self-managed — unsubscribe at any time. Locked threads stop emitting notifications; archived threads stop showing in the forum list.

The Wave-37 trigger on discussion_replies handles the full reply lifecycle: reply_count increments on insert, decrements on hard-delete, decrements on soft-delete (is_active=false), increments back if a reply is restored. The previous version only handled inserts, so deleted replies kept inflating the count — caught in the May-2026 hardening pass.

GIN-indexed search

The search_forum(query, forum_id) RPC walks a GIN-indexed text column built from title || content on threads and content on replies (the actual column name in discussion_threads / discussion_replies is content — earlier doc said "body" by mistake; corrected in commit b896075c). pg_trgm under the hood means "calculater" finds "calculator" threads.

The search bar at /portal/forums scopes to a single forum when one is selected, or searches school-wide when "All forums" is picked. Results rank by recency + match quality.

Spring Fair, Saturday 10am, capacity 200. Parents RSVP from their phones; the system manages the count, opens a waitlist when seats fill, auto-promotes the next family when a "going" cancels, renumbers the queue, fires a notification — "A seat opened up. You're in." The school doesn't manage a spreadsheet. The family doesn't refresh a page. The system handles the count.

Four RSVP states

The school_event_rsvps.response CHECK constraint (migration 00392) accepts four values:

• going — confirmed attending. Counts against the event's rsvp_capacity via party_size (default 1; bring-a-plus-one = 2).
• interested — soft commitment; doesn't count against capacity. Used for headcount estimation beyond the firm "going" set.
• declined — explicit no; useful for schools tracking drop-off arrangements or sibling overlap.
• waitlisted — wanted to attend but capacity was full at RSVP time. Auto-promoted to going when a "going" cancels.

The race-safe RPC

Two parents tap "Going" at the same second on a slot with one seat left. Without server-side serialisation, both would succeed and the count would be wrong. The rsvp_school_event(event_id, response, party_size, notes) RPC (00392) handles the race:

• Locks the event row with FOR UPDATE.
• Sums existing going party_size (excluding caller's own).
• Sum + caller's request ≤ capacity → response stays going.
• Sum > capacity AND waitlist_enabled → response flips to waitlisted; position = MAX + 1.
• Sum > capacity AND waitlist disabled → raises "event is at capacity."
• Upserts the row, returns the persisted state.

RLS rejects direct INSERT — the RPC is the only legitimate write path. The mobile RSVP UI calls this RPC; bypass not possible from the client.

Auto-promoting the waitlist

A "going" parent cancels (response → declined, or row deleted). The trigger fn_school_event_rsvp_release fires process_school_event_waitlist(event_id) (Wave 38, migration 00472):

• Reads remaining capacity (capacity − Σ going party_size).
• Promotes next-position waitlisted rows greedily — smallest party_size that fits the seats.
• For each promoted row: response → going, waitlist_ position → NULL, notification written ("A seat opened up").
• Renumbers remaining waitlist 1..N so "you're #3" really means two ahead.

T-24h reminder

Per-event reminders to every "going" RSVP fire through the hourly cron yess_event_t24_reminders (migration 00538, shipped during the prior audit run). The cron finds events whose start_date = tomorrow and t24_reminder_sent_at IS NULL, then writes one communication-category notification per going RSVP and stamps the event so the batch never re-fires. T-1h reminders are not implemented for school events — the schema uses start_date + nullable start_time, and many calendar rows are all- day (no specific hour anchor), so per-event T-1h has no meaningful trigger window. Conferences (Chapter 9) have a proper scheduled_at timestamp and do carry both T-24h + T-1h.

The event mini-world

/portal/events/<id> opens with the event card (title, date, location, description) + a capacity bar (going / capacity). The parent's own RSVP state shows in a chip top-right (Going / Interested / Declined / Waitlisted #3). The list page at /portal/events inlines RSVP buttons so parents don't have to drill in to act. Mobile parity: apps/mobile/app/(screens)/events/index.tsx (Wave 61) carries the same 4-state UI + the race-safe RPC.

The end-of-term parent-teacher week used to be a printed sign-up sheet on the staffroom wall + the inevitable double-bookings + the family that drove an hour for a slot that wasn't actually theirs. YESS replaces all of it with bookable slots, a race-safe booking RPC, notifications to both teacher and parent on every state transition, and a reminder cron that handles both T-24h and T-1h windows with independent dedup stamps.

Slots, not availability

A teacher publishes slots, not "availability." Each slot is a discrete row in conference_slots (migration 00392) with a start time, duration (5-180 min), max-bookings cap (default 1; a teacher running rolling 10-min slots could allow up to 3 families to queue for the same window), and an optional audience_expression (scope to Form-4 parents only, for instance). The slot is bookable from opens_at onward; the teacher can publish a whole week's worth at 9am Monday and parents see them on the portal immediately.

The race-safe booking

Two parents tap "Book" at the same second on a slot with one seat. The book_conference_slot(slot_id) RPC handles the race:

• Locks the slot with FOR UPDATE.
• Counts existing bookings (excluding caller's own).
• Rejects when count ≥ max_bookings — the second parent gets "slot is full" client-side.
• Inserts the parent_teacher_conferences row when there's room — scheduled_at = slot.opens_at, duration = slot.slot_duration_min, mode = "in_person" by default (parent can switch to virtual if the teacher opted in).
• Fires the 00394 trigger fn_conference_booked_notify: both teacher AND parent get the callback. No silent state.

Five lifecycle states

• booked — parent has reserved; teacher knows.
• confirmed — both sides explicitly confirmed (optional second-touch flow for schools that require pre-confirmation).
• cancelled — either side cancelled. fn_conference_cancelled_notify fires the counterpart notification. Slot returns to bookable capacity.
• completed — conference happened. Set by the teacher post-meeting (optional; many schools leave rows in "booked" until term end).
• no_show — parent didn't arrive. Useful for schools running follow-up flows on no-shows.

T-24h and T-1h reminders

Unlike school events, parent_teacher_conferences.scheduled_at is a proper TIMESTAMPTZ — both T-24h and T-1h windows are meaningful and precise. The cron yess_conference_reminders (migration 00539, shipped during the prior audit run) runs every 15 minutes and handles both batches in one pass:

• T-24h batch: rows where scheduled_at > NOW() AND ≤ NOW() + 24h AND t24_reminder_sent_at IS NULL AND status ≠ cancelled. Queues paired teacher + parent notifications, stamps t24_reminder_sent_at.
• T-1h batch: same shape with t1h_reminder_sent_at column. Independent stamp means a late booking (<24h before) still gets the T-1h reminder even if the T-24h window was missed.

The 15-min cadence is the binding constraint — T-1h precision means worst-case lag entering the T-1h window is ~14m45s. Schools running tight 10-min slot patterns can adjust to */5 if needed; default */15 covers most.

In-person or virtual

Some schools run hybrid conferences. The mode column on parent_teacher_conferences picks one or the other. Virtual mode populates virtual_room_id from chat_rooms — the FK is unconstrained on room_type, so schools attach a custom chat room (or any reusable type) and treat it as the meeting surface. Native WebRTC is deliberately out of scope per the May-2026 close; link- out via the attached chat room handles the join URL.

Most platforms treat student government as a list of names on a wall. YESS treats it as a real workspace — a place where the elected actually do the work. Positions are appointed or elected. Petitions collect signatures against thresholds and notify their target. Meetings carry agendas, then minutes, then motions with vote tallies. Constitutions are versioned and ratified only by passed motions. Town halls are live Q&A with peer upvotes. Office hours are recurring slots students can show up to. And position holders with can_moderate_content=true gain real moderation power on the social feed (Chapter 5) and forums (Chapter 7) via the student_can_moderate() SQL helper — the same gate as staff, on the same rows.

Ten kinds of entity

The workspace lives on ten tables — nine new in 00390 plus student_government_positions from 00104:

• positions — class reps, head prefects, club presidents.
• petitions — citizen-driven requests with signature thresholds.
• signatures — one row per signer per petition.
• meetings — agendas, attendees, minutes.
• motions — proposer + seconder + vote tally.
• office hours — recurring weekly slots per position.
• town halls — scheduled events with live Q&A.
• town-hall questions — peer-submitted + upvoted + answered.
• upvotes — one row per voter per question.
• constitution — versioned text, ratified by a passed motion.

Plus the SQL helper student_can_moderate() — returns TRUE for any active position holder with can_moderate_content=true. Plugged into the social feed and forums RLS so the head prefect moderates without begging the admin. Used in 18 RLS policies.

A full term

Calendar dates, not hours of the day — governance moves at term pace.

• Day 1. Head prefect opens /dashboard/student-government and appoints six class reps + one head girl + one head boy. Each row writes to student_government_positions with is_elected=false, academic year, and a term_end_date. Three positions flagged can_moderate_content=true. Within ten seconds these three have moderation rights on social posts + forum threads via student_can_moderate().
• Day 14. A Form-5 student opens a petition: "Move final exams a week to give more revision time." Targets the head prefect's position; signature threshold = 60; audience expression "all Form-5 students"; closes_at end of next week. INSERT into student_petitions. Wave 48's 00481 trigger fires "petition opened against you" to the head prefect.
• Day 15-18. Students sign. Each row increments the signatures_count denorm (Wave 40's 00473). The 00394 trigger fires milestone notifications every 10 signatures and at the threshold itself — so the target gets a steady drumbeat rather than per-signature spam. At signature 60 the trigger flips status → threshold_met and notifies again.
• Day 19, 5pm. Head prefect calls a meeting. Status → scheduled. Meeting begins; status → live. A motion proposed: "Move final exams to week 12." Proposer + seconder set. Voting opens. Officers tap For / Against / Abstain. Tally updates live via Realtime (00484). Motion passes 7-2-0. Wave 28's 00396 trigger fires "your motion passed" to proposer + seconder.
• Day 19, 5:42pm. Meeting ends. Status → minutes_pending (Wave 35's 00469 auto- transition cron would flip stale meetings 4h after scheduled_at — but here the secretary acts immediately).
• Day 19, 8:30pm. Secretary writes minutes + publishes. Status → published. Wave 42's 00475 trigger fires "minutes published" to all officers. Head prefect writes the petition response. useUpdatePetition sets response + status → responded. Wave 27's 00395 trigger fires notifications to every signer + petition author. Wave 44's 00477 audit trigger writes the action.
• Day 21. A constitution amendment is drafted. useDraftConstitution writes the row with is_current=false. The next meeting passes a motion to ratify it. ratify_constitution(draft_id, motion_id) flips is_current=true. Wave 27's trigger fires "constitution ratified" to officers + drafter. Wave 44's audit trigger logs it.
• Day 30, 4pm. Town hall live. Students submit questions; each writes a row in town_hall_questions. Peers upvote — each upvote writes a row in town_hall_question_upvotes and the denorm upvote_count increments. Questions sort by upvote_count DESC. Host picks the highest-voted, marks it answered with the answer text. Wave 28's 00396 trigger fires "your question was answered" to the asker. Realtime (00484) updates the queue without anyone refreshing.
• Day 91 (end of term). Some petitions never reached threshold; some never got a response within the window. Wave 34's 00468 cron at 04:21 UTC daily auto-closes expired open petitions, flipping status → closed.

Three governance crons

Three crons own the periodic governance work:

• yess_petition_auto_close (00468) — 04:21 UTC daily. Flips open petitions past closes_at to status='closed'.
• yess_meeting_townhall_auto_transition (00469) — 05:01 UTC daily. Flips stale meetings 4h past scheduled_at from 'live' → 'minutes_pending' when the secretary forgot to mark them done; same for town halls past their scheduled window.
• yess_profile_reputation_recompute (00397) — 04:09 UTC daily. Used here because petitions_signed is a reputation factor (Chapter 12) and governance activity feeds it. The cron itself lives under Chapter 12.

(The story-purge cron in 00466 and the scheduled-message promote cron in 00399 are sometimes lumped with these but belong to Chapter 6 + Chapter 13.)

Discovery + join + officer roster were table-stakes. The May-2026 expansion turned clubs into real organisations. Each club has a budget with a transaction ledger and approval workflow. The treasurer files reimbursements; the president approves; the rollup trigger updates spent / allocated. Members earn citations and the club itself wins school-level recognition. The photo gallery enforces parent consent for under-13 students via RLS. The leaderboard combines events held + attendance rate + members + posts published + recognitions into a composite score, refreshed daily.

Nine kinds of entity

• club — name, type, supervisor.
• members — student-side roster (role: member / president / vice_president / secretary / treasurer).
• events — scheduled activities with RSVPs.
• RSVPs — attending / maybe / not_attending (3-state, distinct from the 4- state school-event RSVPs in Chapter 8).
• budgets — per-fiscal-year allocated + spent counters.
• budget transactions — expense / reimbursement / allocation / adjustment with status flow draft → pending → approved | rejected | reversed (5 states; the fifth handles accounting reversals on previously-approved transactions).
• club recognitions — school-level award to the whole club.
• member recognitions — citation to one specific member.
• gallery items — photos with parent- consent gate.

Plus the materialised view club_leaderboard_metrics ranking every club on a composite score.

The transaction ledger

A club budget is a single row carrying allocated + spent + currency for a fiscal year. The actual movement happens in club_budget_transactions. Only approved transactions update the parent budget's counters via the 00391 rollup trigger; the reversed state un-applies the previously- approved delta so the ledger stays consistent.

Wave 45's 00478 notification triggers fire both ways: when a member submits a pending transaction, the treasurer + president + vice president get the callback; when the treasurer or president approves or rejects, the submitter gets the callback. "No silent state."

Two kinds of recognition

Member recognitions — citation to a specific student for a specific contribution. Writes to club_member_recognitions. Wave 46's 00479 trigger fires "You were recognised by [club]" to the recipient with the citation text.

Club-level recognitions — school-level honour to the whole club ("Drama Club of the Year", "Maths Olympiad Champions"). Writes to club_recognition_awards. Wave 47's 00480 trigger fans the celebration to every active member.

Photos with consent

A primary school cannot legally publish photos of under- 13 students without parent consent in many jurisdictions. The club_photo_gallery_items table carries a parent_consent_required boolean. When the flag is set, RLS hides the row from any user whose children appear in the photo unless the school has explicit consent on record. Server-side enforcement; no client-side workaround. Wave 46's 00479 trigger fires "Gallery photo needs review" to the club president + vice president when a new photo lands unapproved; on approve, the trigger fires "Your gallery photo was approved" to the uploader.

A composite ranking

A school with twenty clubs needs a ranking that surfaces the active ones. The materialised view club_leaderboard_metrics combines events_held_count, attendance_rate, member_count, posts_published, recognitions_count, last_active_at into a single composite score. The ranking lives at /portal/clubs/leaderboard. The view refreshes daily; sortable by any column for schools that want different angles.

The identity surface. Bio, banner, interests, pronouns, year of study, faculty, program — university density at the school-management price-point. A follow graph drives the "From people I follow" feed filter. Community badges in four tiers recognise everything from "first reply marked best answer" to "ten years of service." A daily- cron reputation score weights best-answers + badges + post appreciation + helpful replies + petitions signed into a single number that surfaces on the profile hero and the badge catalog leaderboard.

A profile worth filling

The May-2026 expansion (migration 00393) added seven optional fields to user_profiles: bio (free text), banner_url, interests (text array), pronouns, year_of_study, faculty_id, program_id. None are required — primary schools mostly ignore them; universities lean on every one.

The profile mini-world at /portal/profile/<id> renders these into a hero with avatar + banner + name + role + bio + badges + reputation pill, then rooms for Overview / Their posts / Their clubs / Followers / Following / Badges / Reputation. Cross-link rooms (Their posts + Their clubs from Wave 18) surface what the user has authored / joined without forcing the viewer to browse the social feed manually.

The follow graph

One table, three columns: profile_follows with follower_id, followee_id, created_at. Tap "Follow" on a peer's profile — row inserts. Tap "Following" to unfollow — row deletes. The 00394 trigger fires "X started following you" to the followee. Dedup ensures rapid follow → unfollow → follow doesn't re-spam — the notification fires only on the first insert per pair.

The follow graph drives the "From people I follow" filter on /portal/social — the parent who only cares about her own daughter's posts flips one filter chip and the feed narrows. The school itself follows nothing; the graph is student / parent / teacher peer-to-peer.

Four tiers, one catalog

Every school authors its own badge catalog at /dashboard/communication/badges. Each badge has a code (unique per school), label, optional description, and a tier:

• bronze — entry-level. Auto-awardable. The "Helpful" badge (first best-answer in Chapter 7) is the canonical example.
• silver — sustained contribution. Typically manual.
• gold — significant achievement. Manual.
• platinum — extraordinary. Manual; rare.

A badge can be auto-awarded by criteria (the criteria JSON on the catalog row carries the trigger description) or manually by an admin. Wave 29's 00397 seeds the "Helpful" bronze badge per school + fires the auto-award when a student gets their first best-answer marked (Chapter 7's loop). Other badges are manual via the dashboard admin page.

Each award writes a row to profile_badges with UNIQUE(profile_id, badge_id) so the same badge can't be awarded twice. The 00394 trigger fires "You earned the [badge label]" to the recipient with the citation.

A daily recompute

Reputation is a single integer per profile, recomputed nightly by the 00397 cron at 04:09 UTC ('9 4 * * *'). Five weighted factors, confirmed at 00393:266-269:

• best_answers × 15 — the heaviest weight. Helping peers solve a problem is the most valuable community contribution.
• badges × 10 — both auto- and manually-awarded count.
• posts_appreciated × 2 — posts with community-meaningful reactions (love, celebrate, support).
• helpful_replies × 1 — replies on forums that received upvotes.
• petitions_signed × 1 — civic engagement (Chapter 10). Small weight; the floor.

The recompute walks every active profile per school, fills the profile_reputation_scores row, sets last_recomputed_at. The cron is scheduled at 04:09 UTC because dispatcher activity quiets between 03:00 and 05:00 in most regions YESS serves.

A vice-principal notices her form teachers are typing the same "Hi {{parent_name}}, {{student_name}} arrived at {{arrival_time}}, {{minutes_late}} minutes late" sentence twenty times every morning. She opens /dashboard/communication/templates — one page, two surfaces: a template library on top and a scheduled-message queue below.

Authoring a template

"New template" opens a Dialog with five fields:

• Name. Short label (≤80 chars). Shows in the composer's dropdown — "Late-pickup reminder."
• Body. The message text with {{snake_case}} tokens for variables (≤2000 chars; rendered in font-mono so the tokens stand out). The dialog auto-extracts tokens from the body via regex and surfaces them as monospace chips below the textarea.
• Extra variables (optional). Comma- separated. Useful when the composer should prompt for a value the author plans to inline later.
• Scope. Five choices: school / class / subject / department / personal. The first four require communication_chat.moderate; personal scope is open to anyone. The dialog filters the dropdown options based on the caller's permission so non-moderators only see "personal."
• Pin to top. Checkbox. Pinned templates sort first in the composer dropdown; within each pinned/ unpinned group, the list orders by use_count descending.

Save calls useUpsertMessageTemplate.mutateAsync which upserts into message_templates. RLS gates the school-wide writes; the dialog UI mirrors that gate so the admin sees the right choices.

Using a template in the composer

The chat composer (FileText icon, only visible when templates.length > 0) opens a Popover with the scope-filtered template list. Picking a template inserts its raw body into the input — tokens included. The author fills in the values manually, hits send. The server-side render_message_template RPC interpolates final-form when the message is delivered via the dispatcher.

The same template catalog feeds both /dashboard/messages (the staff side) and /portal/messages (the parent + student side). Personal templates only show to their author; class / subject / department templates show to the membership; school templates show to everyone.

The scheduled-message queue

The same admin page carries the queue. A chat message inserted with scheduled_for set to a future timestamp and scheduled_status='queued' sits in messages waiting for its moment. The Wave 31 cron yess_scheduled_message_promote (migration 00399) runs every minute calling promote_due_scheduled_messages():

• Selects messages where scheduled_status='queued' AND scheduled_for <= NOW().
• Flips status to 'sent'.
• Sets sent_at = NOW().
• The dispatcher Edge Function picks them up like any other message and fans through the recipient's channels.

The 60-second cadence means a message scheduled for 3:00pm arrives sometime between 3:00 and 3:01 — acceptable for chat, not used for timing-critical events that need second-precision (those use the Realtime publication path directly).

The admin page lets each scheduled-message author cancel their own queued rows. The list shows the body preview + the relative time (with red "Overdue" tint if scheduled_for < NOW() and the cron hasn't yet promoted). Tap "Cancel" → the row flips to scheduled_status='cancelled' and the dispatcher never sees it.

This handbook is being rebuilt chapter by chapter, in the order the founder approved: 01 ✓ → 06 → 13 → 04 → 02 → 03 → 08 → 09 → 07 → 05 → 11 → 12 → 10. Each chapter's audit commit ships before its DocsSection block replaces the temporary stub above. When the thirteenth chapter lands, the prior /docs/communication/<chapter> sub-page files get deleted in one closing commit and this handbook becomes the only entry point.

The discipline locked for this rebuild: every claim that references code grep-matches code on main; every missing affordance is built before the chapter's prose mentions it; every page that diverges from the design soul is harmonised in the chapter's wave before the prose ships; every chapter ends with a "Last audited" callout naming the gates run and the fixes that shipped.

One nervous system. Thirteen chapters. Kept honest.